Easy formula, but need help please

T

TxJprs

Hope I posted this to the write place. Need help with writing a formul
for a cell. I'm ok with basic stuff, but I think this is one of thos
If, then kind of formula's and don't have a clue on writing them.

I have a total amount in one cell, call it A20. In another cell I nee
a formula that will take the amount in A20, and based on the amount o
it, calculate a down payment. Here are the specifications for it.

If A20 is less than $2000, the full amount is the down payment.

If A20 is between 2000 and 4999, 1/2 of it is the down payment.

If A20 is between 5000 and 9999, 1/3 of it is the down payment.

If A20 is equal or greater than 10000, 1/4 of it is the down payment.

In each case, rounded to nearest dollar amount.

If you could help me write this, that would be great. Thanks i
advance
 
R

Ron Coderre

For your value in Cell A20 . . . .

First, build a small table with these values (I put mine in cell
F1:G4):

0_______1.00
2000____0.50
5000____0.33 <-Actually =1/3
10000___0.25

Then, put this formula in Cell B20:
=ROUND(VLOOKUP(A20,$F$1:$G$4,2,1)*A20,0)

Does that help?

Ro
 
S

Sandy Mann

Late again Sandy! but an alternative:

=IF(A20<2000,A20,ROUND(A20*(0.25+IF(A20<10000,1/12)+IF(A20<5000,1/6)),0))

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
D

Dana DeLouis

Would this idea work?
A20: =ROUND(A20/(4-(A20<10000)-(A20<5000)-(A20<2000)),0)

HTH :>)
 
Top