if then statements with calculations

  • Thread starter Thread starter cmac
  • Start date Start date
C

cmac

I need to make a table that evaluates a number.

If a number (x) is between (A) and (B), then I have to do a calculation (C1)
If a number is between (B) and (C), then I have to do a calculation (C2)
If a number is between C and D, then I have to do a calculation (C3)
If a number is greater than D, then I have to do calculation (C4)

I just can't seem to get the calculations to come out correct.
 
You don't say what you want to do is the number is smaller than A or exactly
A, B, C or D so adjust as required:

=IF(A1="","",IF(A1>10,C4,IF(A1>=7,C3,IF(A1>=5,C2,C1))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sorry it wasn't very clear. in my explanation. It is a sales commission
multiplier.

If the deal is of a certain size, then an extra bonus is paid.

The levels are as follows:
0 - 50 no calculation
51 - 150 multiply the commission by 10%
151 - 250 multiply the commission by 15%
250 multiply the commision by 25%
Click to expand...

I hope this makes it more clear.
 
Assuming that you mean that if the commission is a figure in say B1 and if
the deal is over 50 then add another 10% to the commission etc. then try:

=IF(A1="","",IF(A1>250,B1*1.25,IF(A1>150,B1*1.15,IF(A1>50,B1*1.1,B1))))

or:

=IF(A1="","",B1*LOOKUP(A1,{0,50,150,250},{1,1.1,1.15,1.25}))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Assuming that you mean that if the commission is a figure in say B1 and if
the deal is over 50 then add another 10% to the commission etc. then try:

=IF(A1="","",IF(A1>250,B1*1.25,IF(A1>150,B1*1.15,IF(A1>50,B1*1.1,B1))))

or:

=IF(A1="","",B1*LOOKUP(A1,{0,51,151,251},{1,1.1,1.15,1.25}))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
=IF(A1="","",B1*LOOKUP(A1,{0,50,150,250},{1,1.1,1.15,1.25}))

Oops! didn't mean to sent this one!

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanks for your help!!!

Sandy Mann said:
Assuming that you mean that if the commission is a figure in say B1 and if
the deal is over 50 then add another 10% to the commission etc. then try:

=IF(A1="","",IF(A1>250,B1*1.25,IF(A1>150,B1*1.15,IF(A1>50,B1*1.1,B1))))

or:

=IF(A1="","",B1*LOOKUP(A1,{0,50,150,250},{1,1.1,1.15,1.25}))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
You are very welcome but check out the link that Pete_UK gave you - it is
very good

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
cmac said:
I need to make a table that evaluates a number.  

If a number (x) is between (A) and (B), then I have to do a
calculation (C1) If a number is between (B) and (C), then I have to
do a calculation (C2) If a number is between C and D, then I have to
do a calculation (C3) If a number is greater than D, then I have to
do calculation (C4)
Click to expand...

And if x <= A?

Brute force,

=IF(x<=A,"impossible",IF(x<=B,C1,IF(x<=C,C2,IF(x<=D,C3,C4))))

More elegant,

=CHOOSE(MATCH(x*0.999999999999999,{-1E300;A;B;C;D}),"impossible",
C1,C2,C3,C4)
 
Back
Top