[Solved] Assign sales values to categories

[RobinM17]

I'm trying to get my spreadsheet to work and I've tried various different combinations of "IF" conditions in the formula to no avail.

Basically, the spreadsheet has daily sales figures in column A, plus a running total of days worked in column B.

They're totalled up at the bottom of each column then divided with eachother for a daily average at the top of column C (C1).

What I need to do is get that total in column C to give me a number based on the value of that average.

So, for example;

If C1 is greater than 500, it should show "1"
If it's greater than 1000, it should show "2"
Greater than 1500 and it should show "3"
Greater than 2000, should show "4" and so on...

I just can't get it to work with multiples. "=IF(C1>500; 1)" works perfectly. As soon as I add another condition, it gives an error.

This - "=IF(C1>1500,"3",IF(C1 > 1000, "2", IF(C1 > 500, "1", "")))" doesn't work at all.

Just edited to add that I want this to only show the 1,2,3,4etc in one cell. I can make it work as individual "IF" conditions in separate cells but that's not as neat as I'd like it to be.

Can anyone troubleshoot for me, or give me a better solution to the formula?

Edit: Changed subject, was Struggling with multiple "IF" formula  Make your post understandable by others  -- MrProgrammer, forum moderator

[floris v]

You got your separators wrong. Try

Code: Select all

=IF(C1>1500;"3";IF(C1 > 1000; "2"; IF(C1 > 500; "1"; "")))

.
So, semicolons instead of commas between conditions and operations.

[FJCC]

I wouldn't use nested IF() functions. It gets very unwieldy as the number of categories grows. If the values are evenly spaced, as in your example, you can use the QUOTIENT() function. If they are not evenly spaced, you can use VLOOKUP(). The attached file shows examples of both. CHange the value in C1 to see D1 and E1 change. Because your thresholds are > and not >=, I subtracted 0.01 from the value in C1. You can adjust that fudge factor to whatever makes sense in your case.

[RobinM17]

You got your separators wrong. Try

Code: Select all

=IF(C1>1500;"3";IF(C1 > 1000; "2"; IF(C1 > 500; "1"; "")))

.
So, semicolons instead of commas between conditions and operations.

That's fixed it! Thank you for that, it now works perfectly. Maybe not the most elegant solution but I don't need it to be as long as it's working.