# 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

#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 rangeIF(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 alignyour data. MIKEhttp://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 Amountsor 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 alignyour data.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
December 8, 2014 at 05:49:22
 Try the following:Cell b2 = stock beforeCell c2 = stock nowCell d2= amount soldCell g2=level 1 = 10 cellg3=7.5Cell h2=level2=20 cellh3=9.5Cell i2=level3=30 celli3=10.5Cellj2=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 followingLevel 1 =10Level2 =20Level 3=30Level 4=40I 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 equationPlease DON"T one of those variables may impact the answer.....The more information you supply the more complete answer we can work out.MIKEhttp://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..... :-)MIKEhttp://www.skeptic.com/

Report •