functions...which one?

  • Thread starter Thread starter vwfreak0
  • Start date Start date
V

vwfreak0

I have a string of loan amounts (A9:A202), which round up every
$5,000.00 starting with $35,000.00.

So, $35,000.00/$40,000.00/$45,000.00 and so forth.

Also, I have a string of costs involved with every loan amount
(G9:G202)

O.K., with that said I would like to create a cell that I can enter a
loan amount within A9:A202 and the cell to the right or below will
display the correct fees involved.

Ex: Enter $100,000.00 Loan amount (A9)
Result $250.00 Costs (G9) **this is not the correct cost!!

**One trick: I need the costs to round up to the nearest $5,000.00 loan
amount.

So, anything over $100,000.00 would calculate costs for $105,000.00

<<<<<<ANYONE?????>>>>>>>

Please ask any ?'s as I have a dire need to solve this!!!
 
VWFreak,

For a value in A1, use the formula
=IF(A1=0,0,VLOOKUP(INT((A1-1)/5000)*5000+5000,A9:G202,7))

HTH,
Bernie
 
I copied your formula and received this error: #NAME?

This is the beginning of my chart, it may be of some help:

$35,000 $269
$40,000 $294
$45,000 $319
$50,000 $343
$55,000 $360
$60,000 $377
$65,000 $393
$70,000 $410
$75,000 $426
$80,000 $443
$85,000 $459
$90,000 $476
$95,000 $492
$100,000 $509

$35,000 representing A9, and $269 representing G9

Still trying to find a way to designate a cell for entering the loan
amount of ex. $35,000 and the cell to the right would spit out the
associated answer being $269.

Also, if it could automatically round up to the next answer that would
be awesome. Ex. loan amount of $36,000 would have a charge of $294.
(See chart)
 
I copied your formula and received this error: #NAME?
Click to expand...

Then you copied the formula incorrectly. Make sure that when you
paste it into a cell that you don't have any linebreaks or other
spaces in the formula.

If you contact me privately, I will send you a working example.

HTH,
Bernie
 
email me your question if you want

(e-mail address removed)

-dave

p.s. (off the topic) what kinda vw you got??
 
I got it to work....the whole time, i kept entering the formula in A1
when it should have been in A2. Oh my god, I'm so stupid. Thank you
for your help. My office will be very pleased.

It is a 1965 volkswagen beetle, that I call my (viper killer). Thanks
for askin.
 
any one really using clips for an acetate. (beetle as in volkswagen

----- vwfreak0 wrote: ----

I have a string of loan amounts (A9:A202), which round up ever
$5,000.00 starting with $35,000.00

So, $35,000.00/$40,000.00/$45,000.00 and so forth

Also, I have a string of costs involved with every loan amoun
(G9:G202

O.K., with that said I would like to create a cell that I can enter
loan amount within A9:A202 and the cell to the right or below wil
display the correct fees involved

Ex: Enter $100,000.00 Loan amount (A9
Result $250.00 Costs (G9) **this is not the correct cost!

**One trick: I need the costs to round up to the nearest $5,000.00 loa
amount

So, anything over $100,000.00 would calculate costs for $105,000.0

<<<<<<ANYONE?????>>>>>>

Please ask any ?'s as I have a dire need to solve this!!
 
Krish,

Assuming that the cell in which you input the loan amount is H1 fill in in
cell I1 the formula :

Vlookup(ROUNDUP(H1/5000;0)*5000 ,A2:G202,7,0)
--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top