Caluculating a value based on a range

December 6, 2014 at 20:24:50
Specs: Windows 7
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.

See More: Caluculating a value based on a range

Report •

#1
December 7, 2014 at 08:37:45
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-TO which explains the use of the < PRE > tags to align
your data.

MIKE

http://www.skeptic.com/


Report •

#2
December 8, 2014 at 00:53:46
Thanks Mike, Would it help if I sent you the spreadsheet and if so how do I do that. Regards David

Report •

#3
December 8, 2014 at 03:18:34
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

http://www.skeptic.com/


Report •

Related Solutions

#4
December 8, 2014 at 05:49:22
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.5

Cellc7=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


Report •

#5
December 8, 2014 at 09:01:43
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 say within the range

Also you say:
In order to reduce the amount of variables in this equation
Please DON"T one of those variables may impact the answer.....
The more information you supply
the more complete answer we can work out.

MIKE

http://www.skeptic.com/


Report •

#6
December 9, 2014 at 10:19:00
Hello Mike, I think that you have mistaken my possible solution with what you expected the OP to post.
daycartes

Report •

#7
December 9, 2014 at 10:34:02
daycartes,

You are correct, sorry.
That will teach me to read who's talking..... :-)

MIKE

http://www.skeptic.com/


Report •

Ask Question