Help with formula

L

Lenita

Need some help with a formula. Here is the calculation I need to do:

For every $10 more, 1% more on the price
For every $10 less, 1% less on the price

Any help is appreciated.
 
G

Guest

=INT(A1/10)*.1
$100.00 $1.00
$90.00 $0.90
$80.00 $0.80
$70.00 $0.70
$60.00 $0.60
$40.00 $0.40
$30.00 $0.30
$20.00 $0.20
$10.00 $0.10
 
L

lenitamaria

That would be good if it was a simple linear change, but unfortunately,
it isn't. The problem is that it can be $550 one month and $595 another
month, so the formula had to be "smart" enough to recognize how many
$10 differences there are, if it's more or less and add the 1%
accordingly for every $10 +/-. What do you think?
 
L

lenitamaria

That would be good if it was a simple linear change, but unfortunately,
it isn't. The problem is that it can be $550 one month and $595 another
month, so the formula had to be "smart" enough to recognize how many
$10 differences there are, if it's more or less and add the 1%
accordingly for every $10 +/-. What do you think?
 
F

Frans van Zelm

Hi Lenita,

More or less of what?

Concider this:

F G H
1 materials price
2 100 200 base
3 65 194 less
4 100 200
5 135 208 more
6 300 240 more

The formula in H3 is
=((MROUND(F3;10)-$F$2)/1000+1)*$G$2

MROUND is in the add-in Analysis Toopak.

Frans
 
J

JulieD

Hi

not sure what you're after here, are you wanting the +-1% based on the
difference to the previous month or on a base figure? and what figure is the
1% of? but here's one idea anyway where you're looking at the difference
between B1 and A1 and adding +- 1% of B1 for each whole $10 difference
between B1 & A1

=IF(B1-A1>0,INT((B1-A1)/10)*(1%*B1),ABS(INT((B1-A1)/10))*(-1%*B1))

Cheers
JulieD
 
L

lenitamaria

Thank-you for your help. We're doing a spreadsheet with material
pricing, so vendor has agreed to give a 1% discount on our price based
on the industry price for that month; so if the price, industry-wide
goes up $20, then we will be charged 2% more on our price; if if goes
goes $30, then we are charged 3% less on our price. Hopefully this
makes sense; I am going to try all of our suggestions, but if you have
any more, let me know.
 
Top