Excel Help - &if statements not formatting as accounting or currency?

April 12, 2017 8:32 AM

I'm having trouble with some excel formatting when &if statements are used.

Hi all, I have an excel question for you

Probably overlooked something small but I'm stumped. Working on a spreadsheet where there is a drop down column (in hidden A Column) for NE, SE, Midwest, SW, and Pacific to calculate shipping costs to different regions of the country. The statement reads:

If (D10="NE", 15,0)&if(D10="SE",15.95,0) ...etc.

When I set the cell format to accounting and select "NE" the cell returns the value of 015 instead of $15.00. In fact, no matter what type of cell formatting I use, it always returns "015."

Any clue what I'm doing wrong?

posted by FireStyle to Computers & Internet (2 answers total)

Best answer: The ampersand represents string concatenation and forces Excel to interpret the results of your IF statements as strings. So the result is the string "015", not the number 15, and Excel won't apply any numeric formatting to it.

There's several ways around it (results of these may vary in the details, depending on exactly what you're trying to do): use nested IF statements; use a VLOOKUP table; replace the & with a + ; put a VALUE() around the whole thing.
posted by DevilsAdvocate at 8:44 AM on April 12, 2017

Response by poster: Awesome!! Changing the & to + fixed it. thanks!
posted by FireStyle at 8:57 AM on April 12, 2017