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

On Jan 5, 8:01*am, Kevin H. wrote:
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.



"Ashish Mathur" wrote:
Hi,

Assume that the data below is in range C5:D6. *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." <Kevin wrote in message
...
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 -