I have a spread sheet with product prices.

One column has old prices, another has new prices for some products.

I need a function to look and see if there is a new price and multiply
it by one percentage if it is less than 1000 and multiply it by another
if it is not.

If there is not a new price, then the function needs to look at the old
price for the same numbers.

I have tried several different ways to write this nested if statement.
For the most part, Excel says there is an error. The one time I didn't
get an error it also didn't work correctly.

Does someone have a clue how to do this?


--
stillgreen
------------------------------------------------------------------------
stillgreen's Profile: http://www.excelforum.com/member.php...o&userid=31718
View this thread: http://www.excelforum.com/showthread...hreadid=514219

Hi!

Where is all this stuff?

Be VERY specific!

Biff

"stillgreen" wrote
in message ...

I have a spread sheet with product prices.

One column has old prices, another has new prices for some products.

I need a function to look and see if there is a new price and multiply
it by one percentage if it is less than 1000 and multiply it by another
if it is not.

If there is not a new price, then the function needs to look at the old
price for the same numbers.

I have tried several different ways to write this nested if statement.
For the most part, Excel says there is an error. The one time I didn't
get an error it also didn't work correctly.

Does someone have a clue how to do this?


--
stillgreen
------------------------------------------------------------------------
stillgreen's Profile:
http://www.excelforum.com/member.php...o&userid=31718
View this thread: http://www.excelforum.com/showthread...hreadid=514219

old price column C1 - C422
new price column D1 - D422 (not a new price for every product
though)
our cost column E1

our cost is X times .80 if the price is 1000
and X times .85 if the price is < 1000


--
stillgreen
------------------------------------------------------------------------
stillgreen's Profile: http://www.excelforum.com/member.php...o&userid=31718
View this thread: http://www.excelforum.com/showthread...hreadid=514219

Ok.......

our cost is X times .80 if the price is 1000
and X times .85 if the price is < 1000

What if the price is exactly 1000?

If I understand you, try this:

=IF(AND(D1<"",D1=1000),D1*0.8,IF(AND(D1<"",D1<1 000),D1*0.85,IF(C1=1000,C1*0.8,C1*0.85)))

I used =1000 since exactly 1000 wasn't defined.

Biff

"stillgreen" wrote
in message ...

old price column C1 - C422
new price column D1 - D422 (not a new price for every product
though)
our cost column E1

our cost is X times .80 if the price is 1000
and X times .85 if the price is < 1000


--
stillgreen
------------------------------------------------------------------------
stillgreen's Profile:
http://www.excelforum.com/member.php...o&userid=31718
View this thread: http://www.excelforum.com/showthread...hreadid=514219

Hi

E1=IF(D1="",C1,D1)*(0.8+0.05*(IF(D1="",C1,D1)<1000 ))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"stillgreen" wrote
in message ...

old price column C1 - C422
new price column D1 - D422 (not a new price for every product
though)
our cost column E1

our cost is X times .80 if the price is 1000
and X times .85 if the price is < 1000


--
stillgreen
------------------------------------------------------------------------
stillgreen's Profile:
http://www.excelforum.com/member.php...o&userid=31718
View this thread: http://www.excelforum.com/showthread...hreadid=514219

I hadn't written anything close to this. No wonder it didn't work :)

Thank you for saving me hours of tedious copying and pasting.

Wonder Wyant


--
stillgreen
------------------------------------------------------------------------
stillgreen's Profile: http://www.excelforum.com/member.php...o&userid=31718
View this thread: http://www.excelforum.com/showthread...hreadid=514219