We have a spreadsheet imported from our phone system, yesterday users could highlight cells and autosum average and get average numbers. Excel updated and today no matter what I try on those cells I get #DIV/0! but if I type anywhere else on the same spreadsheet and try it works fine making me think Excel isn't seeing the numbers correctly. I tried a copy/paste multiply by 1 to force numbers to change and also copy paste to another blank section and back and still nothing. Any ideas?
Edit: Also if I just =sum them it always totals 0 which would cause the above error so Excel thinks all the values are 0 for some reason
Check the number format of the problem cell - is it formatted as text instead of a number?
i am somewhat certain in the past when i would start ot enter a formula with the "=" i could then use the scroll wheel on my mouse to move tot eh cell i wanted to pull data from; however, it seems i can no longer do this.
this is on my work PC and I am sure we have not upgraded our version of windows or excel.
the intellimouse option in excel is not checked.
__________________
If I do not come back avenge my death
Sometimes my spreadsheets take forever to open and on the excel splash screen it'll say "connecting/contacting [some random network location". However, once the spreadsheet finally opens and I head over to the connections section, there are no links between the spreadsheet and anything else. There are also no macros in the workbook.
Does anyone know why it seems like excel is trying to connect with something prior to opening the workbook even though I can't find any reference that it should be doing that?
__________________
Much like a sports ticker, you may feel obligated to read this
^ If this happens when you are at work it could just be that the file is downloading from your network drive, and is not saved directly on your computer's harddrive. If you work for a corporate that would be the norm.
Yeah, it might be something along those lines. Pretty much everything we work on is through the network, so it seems odd that only some files would have this weird lag, but I really have no idea how the network servers work.
__________________
Much like a sports ticker, you may feel obligated to read this
Yeah, it might be something along those lines. Pretty much everything we work on is through the network, so it seems odd that only some files would have this weird lag, but I really have no idea how the network servers work.
Do you have any add-ins that are running? Many corporate environments have these that have to query into the network before loading.
__________________
"Teach a man to reason, and he'll think for a lifetime"
Is it possible to have multiple IF/THEN statements in one cell? If I wanted to search a row of cells for the terms red, green and blue and then classify them based on their color - what would that formula look like? For example "if red, then yes", "if green, then yes" and "if blue, then no" - can I do that all in one cell? Right now I can only seem to put one if/then statement in there
Is it possible to have multiple IF/THEN statements in one cell? If I wanted to search a row of cells for the terms red, green and blue and then classify them based on their color - what would that formula look like? For example "if red, then yes", "if green, then yes" and "if blue, then no" - can I do that all in one cell? Right now I can only seem to put one if/then statement in there
You can nest if statements: "if (red then yes, if (green then yes, no)) would be your example. If the cell is red the answer is yes, if not then if the cell is green the answer is yes, if not then the answer is no.
=IF(A1="Red", "Yes",(IF(A1="Green","Yes",(IF(A1="Blue", "No","N/A")))))
you can also assign values to colours and use a vlookup.
I'm sure there's other ways as well. I'd be interested in how other posters would do it differently.
__________________
The Delhi police have announced the formation of a crack team dedicated to nabbing the elusive 'Monkey Man' and offered a reward for his -- or its -- capture.
Last edited by monkeyman; 08-04-2018 at 04:32 PM.
Reason: updated to better fit example.
=IF(A1="Red", "R",(IF(A1="Green","G",(IF(A1="Blue", "B","NULL")))))
you can also assign values to colours and use a vlookup.
I'm sure there's other ways as well. I'd be interested in how other posters would do it differently.
For the Red, Green, Blue example what you have would be the simplest I think. Anything I used to use vlookup for I now IndexMatch, it's much more versatile and can lookup backwards.
The Following User Says Thank You to Hanni For This Useful Post:
Is it possible to have multiple IF/THEN statements in one cell? If I wanted to search a row of cells for the terms red, green and blue and then classify them based on their color - what would that formula look like? For example "if red, then yes", "if green, then yes" and "if blue, then no" - can I do that all in one cell? Right now I can only seem to put one if/then statement in there
You could also use conditional formatting to highlight cells with specific text in them and then you wouldn't need additional cells with formulas in them.
If in a table, you can even sort by cell or text colour.
__________________
"Teach a man to reason, and he'll think for a lifetime"
My new cpu came with excel365 I think now everything takes a second - I'm used to flipping cell to cell really quickly and often I've got to wait a half second before things happen - is this just the nature of the beast with 365? Any benefits to it that I'm not seeing? If I want a simple old version that just runs fast which one is best?
My new cpu came with excel365 I think now everything takes a second - I'm used to flipping cell to cell really quickly and often I've got to wait a half second before things happen - is this just the nature of the beast with 365? Any benefits to it that I'm not seeing? If I want a simple old version that just runs fast which one is best?
I've found 365 to load slower than a typical install, but once open I don't notice any lagging.
Are you running it solely in a browser? If so, I definitely notice delay when operating it that way.
__________________
"Teach a man to reason, and he'll think for a lifetime"
Speaking of simple old versions vs 365, how do I buy an old version? I've found navigating the site impossible as it keeps prompting 365 renewal. And how much is an old version of office ie 2013?
An environment in which a person encounters only beliefs or opinions that coincide with their own, so that their existing views are reinforced and alternative ideas are not considered.
Anybody know if you can combine two excel workbooks into one, automatically? I have two workbooks that are generated. I would like to have one workbook that would pull these two into it, but without any intervention by me. This is so easy by hand by just dragging and dropping the sheets into one workbook, but I would like to automate the process so that when the files are generated, I will be able to just have a third file that has both of these sheets as part of it. Maybe this needs to be done with VBA? Also, can VBA scripts be run from the command line?
I've got a strange one. I have a range of dates in column A and times (h:m:s) in column B. I have the start and the end. How can I fill in everything in between?
Ex:
Cell A2: 1-Aug-18 Cell B2: 13:22:52
Cell A866: 1-Sep-18 Cell B866: 13:22:52
How can I fill in everything in between? There are 864 rows between the start and end date/time. I'm not having much luck in google trying to figure this out, or even how to ask it. If someone from CP understands what I'm saying and knows how to do this, I'll owe you a solid one day!