working on my sales teams commission calculator. I know there's an easy formula to use but am beating my head against the wall here. I want cell 'S6' to use a % range (cells J2 through J7) 'IF' cell 'M3' is 25% or less.
I then want the same cell (S6) to use cells 'K2 through K7' 'IF' cell S6 is then 26% to 50% etc ....
Select different ranges using IF formula
-
Re: need help on 'IF' formula
I guess what you need might be a combination of array and formula(s). However until you can show all a working sample Excel file, I don't think anyone has any clear idea what you need.
Have a nice day!
Yura -
Re: Select different ranges using IF formula
You need to explain what function the formula in S6 is performing on the range. Also please confirm that in:
Quote
I then want the same cell (S6) to use cells 'K2 through K7' 'IF' cell S6 is then 26% to 50% etc ....
S6 should be M3 since it is not possible to perform a calculation for a cell based on the value of the same cell. -
Re: Select different ranges using IF formula
You can try using this function at R6
=IF(AND($M$3>=0%,$M$3<26%),L6,0)+IF(AND($M$3>=26%,$M$3<51%),M6,0)+IF(AND($M$3>=51%,$M$3<76%),N6,0)+IF(AND($M$3>=76%,$M$3<101%),O6,0)+IF(AND($M$3>=101%,$M$3<126%),P6,0)+IF(AND($M$3>=126%,$M$3<150%),Q6,0)
Another option is to use nested "IF" function but such function have limitation of 7 layers only.
With above formula you can have more than 7 layers
Have a nice day!
Yura -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!