How do I enter a formula that provides the following answer;

If a cell value is between two amounts I want to show a different answer.

EG: totals sales equal (a figure will appear in the cell).. This will equate to commission depending on the sales volume.

If sales volume equals volume level 1 the commission will be at 7.5%.

If sales volume equals level 2 the commission will be at 9.5% on the total volume.

If sales volume equals level 3 the commission will be 10.5% of the total volume.

If sales volume equals level 4 the commission will be 11.5% of the total volume.

I can do level 1 and 4. Not sure how to calculate levels 2 or 3 so that the answer is provided only if it is within the range otherwise leave blank.

Please read what you posted,

and remember we know nothing about what your doing,

and we cannot see what your spread sheet looks like from where we are sitting.The best I can do for now is a Nested IF that looks like:

=IF(Somecell contians Level_1 then Someothercell equals Anothercell times 7.5%,

IF(Somecell contians Level_2 then Someothercell equals Another cell times 9.5%, but only if it is within an unknown range

IF(Somecell contians Level_3 then Someothercell equals Anothercell times 10.5% but only if it is within an unknown range,

IF(Somecell contians Level_4 then Someothercell equals Another cell times 11.5%,

))))Post a small sample of your spreadsheet, after reading this

HOW-TOwhich explains the use of the < PRE > tags to align

your data.

MIKE

Thanks Mike, Would it help if I sent you the spreadsheet and if so how do I do that. Regards David

If you post a small sample of your spreadsheet, that should be sufficient.

Need to have more info, like Column Letters & Row Numbers and the Amounts

or other data that go with them. The nuts & bolts of what your doing.First read the HOW-TO which explains the use of the < PRE > tags to align

your data.MIKE

Try the following: Cell b2 = stock before

Cell c2 = stock now

Cell d2= amount sold

Cell g2=level 1 = 10 cellg3=7.5

Cell h2=level2=20 cellh3=9.5

Cell i2=level3=30 celli3=10.5

Cellj2=level4=40 cellj3=11.5Cellc7=commission=IF(D2>J2,D2*J3,IF(D2>I2,D2*I3,IF(D2>H2,D2*H3,IF(D2>G2,D2*G3,IF(D2>0,D2*1)))))

In order to reduce the amount of variables in this equation I have assumed the following

Level 1 =10

Level2 =20

Level 3=30

Level 4=40

I have also assumed that for sales between 1 and 10 the commission rate is 1

Is this what your data looks like: B C D G H I J 1) Stock before Stock now Amount sold Level 1 Level 2 Level 3 Level 4 2) $40.00 10 20 30 40 3) 7.50% 9.50% 10.50% 11.50% 4) 5) 6) 7) $4.20 Your formula: =IF(D2>J2,D2*J3,IF(D2>I2,D2*I3,IF(D2>H2,D2*H3,IF(D2>G2,D2*G3,IF(D2>0,D2*1)))))

So where are you having problems?Is it with this:

I can do level 1 and 4. Not sure how to calculate levels 2 or 3 so that the answer is provided only if it is within the range otherwise leave blank.If it is, then we need more information.

What are the constrains you mean when you saywithin the rangeAlso you say:

In order to reduce the amount of variables in this equation

PleaseDON"Tone of those variables may impact the answer.....

The more information you supply

the more complete answer we can work out.MIKE

Hello Mike, I think that you have mistaken my possible solution with what you expected the OP to post.

daycartes

daycartes, You are correct, sorry.

That will teach me to read who's talking..... :-)MIKE

Ask Your Question

Weekly Poll