Easy formula, but need help please

[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

 

[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

 

[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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Dana DeLouis]

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

HTH :>)