Conditional PRODUCT or SUM

[Kevin H.]

I'm not sure this is possible or if I'm even going about it the right way...
but I'd like to know if there is a function (or combination of functions)
that I could insert into the DISCOUNTED PRICE column to determine whether to
multiply or subtract the DISCOUNT from the PRICE, depending on what is
entered in the DISCOUNT column. Some discounts are a percentage and some are
dollars off, so I need the spreadsheet to be capable of knowing which type of
discount is entered to return the correct DISCOUNTED PRICE result.

PRICE DISCOUNT DISCOUNTED PRICE
$50.00 *.1
$50.00 -10

I could also set it up with columns differentiating between a percentage
discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE
function that would know which column has data in it and whether to multiply
or subtract the data?

PRICE % OFF $ OFF DISCOUNTED PRICE
$50.00 10%
$50.00 $10.00

Am I making this more complicated than it has to be?

 

[Per Jessen]

Hi

With price in column A and discounts in column B, use this formula. Just
enter the discount in $ without the minus sign:

=IF(B2<1,A2-A2*B2,A2-B2)

Regards,
Per

 

[Sheeloo]

My choice would be to have a 'Discount Type' col and use an IF statement to
decide whether to multiply or subtract
=IF(C1="Yes",A1*B1,A1-B1)

Yes indicates percentage and any other value will indicate dollar amount.

Also you can check if value in the discount column is less than say 0.20
(assuming 20% is your highest percentage) [value in percentage column would
be definitely less than 1 ... if you do not have SUBTRACT discount of less
than 1 then you can compare the value to 1 and choose what to do...

 

[Pete_UK]

Two examples are not a lot to go on, but bear in mind that a
percentage will always be less than 1, whereas presumably if you want
to reduce the dollar amount the discount will always be more than
$1.00? If this is the case then you can use one column and your
formula would be something like this in C2:

=IF(B2="",IF(A2="","",A2),IF(B2<1,A2*(1-B2),A2-B2))

Then just copy it down. This allows you to leave the discount column
blank if there is no discount, but you can enter 25%, for example, or
$2.50 into the discount column and the discounted price should be
correct.

Hope this helps.

Pete

 

[Kevin H.]

Wow. Thanks for the fast response from Sheeloo, Per Jenssen, and Pete-UK!
Although I would prefer not to have the extra column, Sheeloo's solution
seems the best for me. The other solutions would work, except sometimes the
discount in dollars is less than $1.00, which would cause the DISCOUNTED
PRICE column to calculate a $0.50 discount as a 50% discount! That's my
fault... I should have provided more information... so thanks for the
suggestions everyone!

My choice would be to have a 'Discount Type' col and use an IF statement to
decide whether to multiply or subtract
=IF(C1="Yes",A1*B1,A1-B1)

Yes indicates percentage and any other value will indicate dollar amount.

Also you can check if value in the discount column is less than say 0.20
(assuming 20% is your highest percentage) [value in percentage column would
be definitely less than 1 ... if you do not have SUBTRACT discount of less
than 1 then you can compare the value to 1 and choose what to do...

I'm not sure this is possible or if I'm even going about it the right way...
but I'd like to know if there is a function (or combination of functions)
that I could insert into the DISCOUNTED PRICE column to determine whether to
multiply or subtract the DISCOUNT from the PRICE, depending on what is
entered in the DISCOUNT column. Some discounts are a percentage and some are
dollars off, so I need the spreadsheet to be capable of knowing which type of
discount is entered to return the correct DISCOUNTED PRICE result.

PRICE DISCOUNT DISCOUNTED PRICE
$50.00 *.1
$50.00 -10

I could also set it up with columns differentiating between a percentage
discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE
function that would know which column has data in it and whether to multiply
or subtract the data?

PRICE % OFF $ OFF DISCOUNTED PRICE
$50.00 10%
$50.00 $10.00

Am I making this more complicated than it has to be?

 

[Pete_UK]

You're welcome.

If your percentage discounts were never more than 50%, you could amend
my formula to:

=IF(B2="",IF(A2="","",A2),IF(B2<0.5,A2*(1-B2),A2-B2))

in order to allow you to have a discount of $0.50 or more.

Hope this helps.

Pete

Wow. Thanks for the fast response from Sheeloo, Per Jenssen, and Pete-UK!
Although I would prefer not to have the extra column, Sheeloo's solution
seems the best for me. The other solutions would work, except sometimes the
discount in dollars is less than $1.00, which would cause the DISCOUNTED
PRICE column to calculate a $0.50 discount as a 50% discount! That's my
fault... I should have provided more information... so thanks for the
suggestions everyone!

My choice would be to have a 'Discount Type' col and use an IF statement to
decide whether to multiply or subtract
=IF(C1="Yes",A1*B1,A1-B1)

Yes indicates percentage and any other value will indicate dollar amount.

Also you can check if value in the discount column is less than say 0.20
(assuming 20% is your highest percentage) [value in percentage column would
be definitely less than 1 ... if you do not have SUBTRACT discount of less
than 1 then you can compare the value to 1 and choose what to do...

"Kevin H." wrote:

- Show quoted text -

 

[Ashish Mathur]

Hi,

Assume that the data below is in range C56. In cell E5, enter the
following formula and copy down =IF(CELL("format",D5)="P0",C5*(1-D5),C5-D5)

PRICE DISCOUNT
$50.00 *.1
$50.00 -10

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Kevin H.]

Thanks for the suggestion! My range is E5:F6, so I adjusted your formula and
inserted it into G5. It seems to handle a flat discount (a dollar amount)
just fine, but I get errors when I insert a percentage. E5 and G5 are
formatted as currency and F5 is formatted as a general number. Is there
something I have to do to F5 to get the formula in G5 to recognize it as a
percentage? I tried formatting it as a percentage, but it treated the number
as a dollar amount. When I put *.1 as I did in my example, it returns a value
error.

I actually plan on taking this a step further once I get it figured out. I
intend to take the formula results and divide it by SIZE to get a discounted
$/UNIT. I provided a simplified example before, but these are two rows
showing what I'd like my actual spreadsheet to look like. Percentage
discounts are formatted as percents and flat rate discounts are formatted as
currency to help you see what I'm trying to do. Of course, I'd rather not
have to manually format each cell as a percent or currency if possible. My
"dream formula" would be inserted into I5:I6, etc.

E F G H I
4 PRICE DISCOUNT SIZE UNIT $/UNIT
5 $1.97 10% 18 fl oz $0.2047
6 $11.33 $1.00 64 loads $0.1614

I really appreciate all the help and quick responses and I think I can make
it work based on Sheeloo's suggestion. But, ideally, I'd rather not add the
extra column if there is a single "dream formula" that I can insert at the
end. I'm hoping your solution works and there's just something that I'm doing
wrong when I execute it.

 

[Kevin H.]

Thanks for the follow-up. I can't really say that the discounts won't be more
than 50%. The only guarantee is that the flat-rate (dollar amt) discounts can
be any amount and that the percentage discounts will never be 100% off.

You're welcome.

If your percentage discounts were never more than 50%, you could amend
my formula to:

=IF(B2="",IF(A2="","",A2),IF(B2<0.5,A2*(1-B2),A2-B2))

in order to allow you to have a discount of $0.50 or more.

Hope this helps.

Pete

Wow. Thanks for the fast response from Sheeloo, Per Jenssen, and Pete-UK!
Although I would prefer not to have the extra column, Sheeloo's solution
seems the best for me. The other solutions would work, except sometimes the
discount in dollars is less than $1.00, which would cause the DISCOUNTED
PRICE column to calculate a $0.50 discount as a 50% discount! That's my
fault... I should have provided more information... so thanks for the
suggestions everyone!

My choice would be to have a 'Discount Type' col and use an IF statement to
decide whether to multiply or subtract
=IF(C1="Yes",A1*B1,A1-B1)

Yes indicates percentage and any other value will indicate dollar amount.

Also you can check if value in the discount column is less than say 0.20
(assuming 20% is your highest percentage) [value in percentage column would
be definitely less than 1 ... if you do not have SUBTRACT discount of less
than 1 then you can compare the value to 1 and choose what to do...

"Kevin H." wrote:

I'm not sure this is possible or if I'm even going about it the right way...
but I'd like to know if there is a function (or combination of functions)
that I could insert into the DISCOUNTED PRICE column to determine whether to
multiply or subtract the DISCOUNT from the PRICE, depending on what is
entered in the DISCOUNT column. Some discounts are a percentage and some are
dollars off, so I need the spreadsheet to be capable of knowing which type of
discount is entered to return the correct DISCOUNTED PRICE result.

PRICE DISCOUNT DISCOUNTED PRICE
$50.00 *.1
$50.00 -10

I could also set it up with columns differentiating between a percentage
discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE
function that would know which column has data in it and whether to multiply
or subtract the data?

PRICE % OFF $ OFF DISCOUNTED PRICE
$50.00 10%
$50.00 $10.00

Am I making this more complicated than it has to be?- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

Kevin,

If you really want to confine the discount to one column, then one way
you could get it to work would be to enter the percentage discounts as
negative and the flat rate discounts as positive (or vice versa). Then
the formula can take account of negative and positive amounts in
different ways.

Hope this helps.

Pete