Change price if cost increases x amount?

[Guest]

I don’t even know what I would like to do is even possible or feasible with excel 2000. I am trying to make a product and price list and every time a shipment comes in update cost and pricing.
I have a column for
cost per unit(which is a formula (extended price/amount received)
and 3 different price columns depending on how many units you buy, which is determined by different percentages. Example price #1 has markup of 25%, price # 2 markup 45%, etc
What I would like to do is when entering cost per unit, if the price increases or decreases a certain amount or percent, it would change the price and highlight it red. If it doesn’t leave the price as it was.
Any help would be appreciated

Thanks Pam

 

[Frank Kabel]

Hi Pam
not sure I understood you. I didn't get the link between your cost
entry and the price (that is the algorithmn which determines if the
prices has to be changed). Maybe you can give an example with sample
data (posted as text to this NG - no attachment please) nd your
espected result

 

[Frank Kabel]

????

 

[Guest]

I will try to explain myself a little better

1 gal of Iodine cost $10.00
resell 1 gal at $13.00 (cost * markup
resell ½ gal at $ 7.50 (cost * markup/unit

If the next time we buy Iodine and our cost has increased to $11.00 I would want to change the resell price t
1 gal @ $14.3
½ gal @ $ 8.2
But if it only increased to $10.10 I would not want to change to price to $13.13 I would like to leave it at $13.00
If the price did change I would like the color to change so I would be easy to see what prices changed
Hope this explains it a little better
Thanks again Pa
p.s. Sorry about other message, I hit the enter button by mistake.

 

[Frank Kabel]

Hi Pam
so your markups in this example are 30% and 50% respectively. One
question: Do you really want to override the existing prices. I would
strongly recommend to use an adjacent row. Otherwise you'll use your
paper trail, etc. also the other solution would require some VBA event
macros to check for cell changes, storing old results, etc. Though this
can be done it would by a little bit complicated.
So your choise Do you really want this? And if yes, why is the
solution with additional columns/rows not suitable for you

--
Regards
Frank Kabel
Frankfurt, Germany

I will try to explain myself a little better.

1 gal of Iodine cost $10.00
resell 1 gal at $13.00 (cost * markup)
resell ½ gal at $ 7.50 (cost * markup/unit)

If the next time we buy Iodine and our cost has increased to $11.00 I

would want to change the resell price to

1 gal @ $14.30
½ gal @ $ 8.25
But if it only increased to $10.10 I would not want to change to

price to $13.13 I would like to leave it at $13.00.

If the price did change I would like the color to change so I would

be easy to see what prices changed.

 

[Guest]

Thanks! I can make another column without a problem. I just didn't know if that was the best way to do it. So I would make old price column and a new price column?? Then use the if function?? Thanks agian for your help. Pa

 

[Frank Kabel]

Hi Pam
yes that is right. To give you an example
A B C
1 old new
2 costs 10 11
3 price 1gal =$B$2*1.3 =*1
4 price 1/2 gal. =$B$2*1.5 =*2

---
*1: =IF(ABS($C$2*1.3-$B$3)>0.5,$C$2*1.3,$B$3)
*2: =IF(ABS($C$2*1.5-$B$4)>0.5,$C$2*1.5,$B$4)

this will insert a new price only if it differs more than 0.5 from the
old price


--
Regards
Frank Kabel
Frankfurt, Germany

Thanks! I can make another column without a problem. I just didn't

know if that was the best way to do it. So I would make old price
column and a new price column?? Then use the if function?? Thanks agian
for your help. Pam

 

[Guest]

Thanks! That will get me started on the right track. Pam