Easy formula, but need help please
 

Hope I posted this to the write place. Need help with writing a formula
for a cell. I'm ok with basic stuff, but I think this is one of those
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 need
a formula that will take the amount in A20, and based on the amount of
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 in
advance.


--
TxJprs
------------------------------------------------------------------------
TxJprs's Profile: http://www.excelforum.com/member.php...o&userid=25710
View this thread: http://www.excelforum.com/showthread...hreadid=391276

For your value in Cell A20 . . . .

First, build a small table with these values (I put mine in cells
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?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=391276

That did the trick. Thank you very much!


--
TxJprs
------------------------------------------------------------------------
TxJprs's Profile: http://www.excelforum.com/member.php...o&userid=25710
View this thread: http://www.excelforum.com/showthread...hreadid=391276

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

Replace@mailinator with @tiscali.co.uk


"TxJprs" wrote in
message ...

Hope I posted this to the write place. Need help with writing a formula
for a cell. I'm ok with basic stuff, but I think this is one of those
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 need
a formula that will take the amount in A20, and based on the amount of
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 in
advance.


--
TxJprs
------------------------------------------------------------------------
TxJprs's Profile:
http://www.excelforum.com/member.php...o&userid=25710
View this thread: http://www.excelforum.com/showthread...hreadid=391276

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

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"TxJprs" wrote in
message ...

Hope I posted this to the write place. Need help with writing a formula
for a cell. I'm ok with basic stuff, but I think this is one of those
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 need
a formula that will take the amount in A20, and based on the amount of
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 in
advance.


--
TxJprs
------------------------------------------------------------------------
TxJprs's Profile:
http://www.excelforum.com/member.php...o&userid=25710
View this thread: http://www.excelforum.com/showthread...hreadid=391276