Excel help

[Robmarriott]

Or: I've had enough of Googling it and need an adult.

I'm trying to build myself a bonus tracker at work and I can't work out how to get the result I'm after using "IF" conditions.



This is obviously not using the real numbers but it's how the spreadsheet works. Column A is daily sales value, Column B is days worked.

Cell C1 is the average, using "=sum(A33/B33)"

What I need is a formula which gives me a value based on the contents of cell C1;

If C1 is greater than 300, it needs to read 1
If it's greater than 350, it should read 2
Greater than 400 and it should be 3... and so on.

I've managed to get it so it'll show "1" in one cell, "2" in another, "3" in another using individual "IF"s, LIKE "=IF(C1>350;2)", "=IF(C1>400;3)" etc, so the two showing 0 are working as they are looking for values of 550 and 600, which the average does not exceed.



But that's not what I want. I want it to just be one cell.

Can anyone help me?

[David_M]

I think the easiest way is to use a separate lookup table with the bands in, and xlookup

https://excelkid.com/xlookup-value-between-two-num...

[bigandclever]

Use IFS not IF. Or if you really want to use IF, nest them.

eg https://exceljet.net/functions/ifs-function

[JulietRomeo]

Sorry been in meetings since 5am and now tucked up in bed awaiting a second wind to go downstairs for a bevvy.

Nestedif is what will do the job but I’m not good enough to write it verbatim without being in front of excel. Try here https://www.wallstreetmojo.com/excel-nested-if-fun...

[Wilmslowboy]

You only appear to be looking for values greater than, so almost all the numbers would meet the first criteria


Try this (need to change the value to suit, I only have 4 conditions it checks for)

=IF(AND(C1>=0, C1<=100), 1, IF(AND(C1>=101, C1<=200), 2, IF(AND(C1>=201, C1<=300), 3, IF(AND(C1>=301, C1<=400), 4, IF(C1>=401, 4, "Out of Range")))))

Edited by Wilmslowboy on Friday 22 March 22:00

[Robmarriott]

Thanks for the replies. I made it work in what might be a bit of a janky way, but janky and working is good enough.

I'd tried a formula like this "=IF(C1>1500,"3",IF(C1 > 1000, "2", IF(C1 > 500, "1", "")))"

and just needed to change to "=IF(C1>1500;"3";IF(C1 > 1000; "2"; IF(C1 > 500; "1"; "")))" for it to work. Needed semicolons instead of commas before each IF.

Now it does exactly what I want it to. I'm sure there's a more elegant solution but I'm happy with the bodge.

[Mr Pointy]

=IFS(C1>600,"7",C1>550,"6",C1>500,"5",C1>450,"4",C1>400,"3",C1>350,"2",C1>300,"1")

[Mr Pointy]

.
https://support.microsoft.com/en-us/office/ifs-fun...

[pingu393]

Interesting.

IFS is not a function in the version of Excel that I use.

IF and IFERROR are the only two. I would have to use nested if. IFS looks like a good upgrade.

[Mr Pointy]

pingu393 said:

Interesting.

IFS is not a function in the version of Excel that I use.

IF and IFERROR are the only two. I would have to use nested if. IFS looks like a good upgrade.

Yes, I think it's a relatively recent addition. Nested IF statements are the work of the devil.

[Robmarriott]

pingu393 said:

Interesting.

IFS is not a function in the version of Excel that I use.

IF and IFERROR are the only two. I would have to use nested if. IFS looks like a good upgrade.

Same, I tried the IFS but it didn't like it. I've had to use nested IF like this, but worse because the input and output numbers all have more digits.

=IF(C1>450;"9";IF(C1>400;"8";IF(C1>350;"7";IF(C1>300;"6";IF(C1>250;"5";IF(C1>200;"4";IF(C1>150;"3";IF(C1>100;"2";IF(C1>50;"1";"")))))))))

Like I said, not elegant!

Edited by Robmarriott on Friday 22 March 23:22

[Rayny]

Just a quick thought:
If you use the nested IF formula - And at a later date need to change any of the criteria, then you will need to edit all of the formulae.

If you use a LOOKUP table, then any changes to the criteria can simply be done once in the LOOKUP set.

[Boulderpaul]

Providing you want to start at values greater than 300 and in steps of 50 then try this:

=ROUNDUP( (C1-300)/50)

[Ceeejay]

Don’t you just need a calculation

=ROUNDDOWN(((C1-250)/50),0)

So 299 =0
300 =1
350=2
Etc…

[blueg33]

I would use a lookup table. As someone else said that’s will enable you to change the rates more easily than changing the formula

As a general tip, I find chat gpt is good excel formulas

[Glade]

bigandclever said:

Use IFS not IF. Or if you really want to use IF, nest them.

eg https://exceljet.net/functions/ifs-function

Nice. Ha, I think I am quite handy with excel but I didn't know that. Might have to educate myself on other more recently introduced functions.

[dobbo_]

blueg33 said:

I would use a lookup table. As someone else said that’s will enable you to change the rates more easily than changing the formula

As a general tip, I find chat gpt is good excel formulas

I was fighting VLOOKUP and XLOOKUP for ages, reading stack overflow, MS articles, everything.

Asked Gemini (Google version of chatGPT) using natural language and it wrote me the formula first time

So yes, an excellent general tip

[sparkyhx]

David_M said:

I think the easiest way is to use a separate lookup table with the bands in, and xlookup

https://excelkid.com/xlookup-value-between-two-num...

my first thought too

[Lawrence5]

Have a look at switch - good where nested if statements would have been used. Saves messing with brackets