if then function

[Guest]

I am trying to construct a formula that will take colum "b2" and determine if
it is greater than $100,000; if the formula is, I need to take the amount
that is greater than 100,000 and multiply it by .005, then add the 100,000 *
..00575.
I have been away from Excel for a year, and I know that it can be done, but
I'm too rusty and keep getting errors.
Any suggestions would be appreciated.
Thank you,
Pam

 

[Pete_UK]

Try this, Pam:

=IF(B2>100000,(B2-100000)*0.005+100000*0.00575,B2*0.00575)

I have assumed that if B2 is less than or equal to 100,000 then you
would want to multiply it by .00575.

Hope this helps.

Pete

 

[Guest]

One way

=MAX(B2-100000,0)*0.005+MIN(100000,B2)*0.00575

to just get the values greater than 100000 use

=MAX(B2-100000,0)*0.005


Regards,

Peo Sjoblom

 

[Gary]

If at all i understood right.....

=if(b2>100000,((b2*.005)+(100000*.00575)))

see if it works.

GARY

 

[Guest]

I used this function, but thanks Pete, your's works too, and I haven't tried
the min max yet, but will.
Thanks for all your help

=IF(H8<=100000,SUM(H8*0.00575),SUM(H8-100000)*(0.005)+(575))

 

[Gary]

=if(b2>100000,((b2*.005)+(100000*.00575)),"")

forgot to put the Value If False. now try.

 

[Bernard Liengme]

Pam,
You do not need the SUM function here:
Wrong: =IF(H8<=100000,SUM(H8*0.00575),SUM(H8-100000)*(0.005)+(575))
Right: =IF(H8<=100000,(H8*0.00575),(H8-100000)*(0.005)+(575))
or even:=IF(H8<=100000,H8*0.00575,(H8-100000)*0.005+575)

best wishes

 

[Pete_UK]

Thanks for feeding back, Pam.

Pete