# Select different ranges using IF formula

• 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 ....

• 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

Right, what I need the worksheet to do is, based on the % in cell MN3, cells R 6 to 10 to automatically choose the % ranges listed in columns L through Q.

An example would be, if Sales rep X had a closed won total of \$68,752, the '% to Quota' would be 92%. I want a formula to then choose the %'s in column 'O' (76% to 100% of Quota) in column 'R'.

## Files

• 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!