Formula re value in column C or D, calculations made on either in column E?

[StargateFan]

I have a situation where not knowing what the type of calculation I'm
doing is probably the reason why googling didn't bring up any results.
So I'll try to explain well what I'm trying to do. I have values in
column C in a spreadsheet that are pre-discount. Some items get a
discount and can then be used in finding out totals. But other values
in the sheet don't get discounted. So 2 types of prices.

B2 shows a base price of, say, $10, and C2 shows a discounted price of
$8 for this as it's elible for a 20% discount. D2 lists the # of
people being invited to this event, which is 25, which makes for $8
discounted price x 25 people which means a total cost for this
category of $200 in E2. I did this page manually and tailored the
formula for each scenario. For the prices that had no discount, I
manually put a formula in column E that would multiply the base price
by the # of people and disregarded the empty discount price cells in
column C and adjusted the formula to use the discounted price instead
when applicable.

But in order to make this spreadsheet "publishable", would rather have
a formula that does the calculation on either column without my
"fixing" anything manually.

I'm sure this is basic stuff but didn't find anything googling ... Pls
advise. Thanks! D

 

[Jim Cone]

=IF(ISNUMBER(C2),C2,B2)*D2
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Lottery Numbers.xls... Excel picks numbers for 14 state/national (USA) lotteries - in the free
folder)



"StargateFan" <IDon'(e-mail address removed)>
wrote in message
news:[email protected]...

 

[GS]

StargateFan brought next idea :

I have a situation where not knowing what the type of calculation I'm
doing is probably the reason why googling didn't bring up any results.
So I'll try to explain well what I'm trying to do. I have values in
column C in a spreadsheet that are pre-discount. Some items get a
discount and can then be used in finding out totals. But other values
in the sheet don't get discounted. So 2 types of prices.

B2 shows a base price of, say, $10, and C2 shows a discounted price of
$8 for this as it's elible for a 20% discount. D2 lists the # of
people being invited to this event, which is 25, which makes for $8
discounted price x 25 people which means a total cost for this
category of $200 in E2. I did this page manually and tailored the
formula for each scenario. For the prices that had no discount, I
manually put a formula in column E that would multiply the base price
by the # of people and disregarded the empty discount price cells in
column C and adjusted the formula to use the discounted price instead
when applicable.

But in order to make this spreadsheet "publishable", would rather have
a formula that does the calculation on either column without my
"fixing" anything manually.

I'm sure this is basic stuff but didn't find anything googling ... Pls
advise. Thanks! D

How many people does it take to qualify for the 20% discount?

For example, in E2...
=IF(NumPeople>=25,(BasePrice*80%)*NumPeople,BasePrice*NumPeople)

...where
NumPeople=$D2, BasePrice=$B2

Just copy this down as needed.

 

[GS]

Note that my suggestion eliminates the need for using a discounted
price in C2. To use a discount percent in C2...

In C2: 80%
In E2...
=IF(NumPeople>=25,(BasePrice*Discount)*NumPeople,BasePrice*NumPeople)

**Note that using defined names makes the formula user-friendly. I find
this is more appreciated by other users since it makes the spreadsheet
easier to understand than using cell refs or otherwise cryptic
formulas. -No offense toward Jim's offering as it's definitely more
efficient. However, it's likely to be understood by only highly skilled
users; ..not something I like to do in projects being "published" for
access/use by others.<IMO>

 

[StargateFan]

StargateFan brought next idea :

How many people does it take to qualify for the 20% discount?

For example, in E2...
=IF(NumPeople>=25,(BasePrice*80%)*NumPeople,BasePrice*NumPeople)

..where
NumPeople=$D2, BasePrice=$B2

Just copy this down as needed.

Oh, sorry. I didn't anticipate that, but you're right. In some
cases, the numbers sometimes are what affect pricing. We're getting a
group rate on the services because we've hired one of their
conference rooms and have ordered a meal, so that is what triggered
the discount in this case. We could have had a few less people (or a
lot more, of course) and we would still have gotten the benefit. But
it's just the services and not the food and wine, etc., so having the
spreadsheet good-to-go as-is without manually changing the formulas to
suit the case will be great.

Good point and I'll keep this code for future reference because, yes,
sometimes a threshold number affects pricing.

 

[StargateFan]

I have a situation where not knowing what the type of calculation I'm
doing is probably the reason why googling didn't bring up any results.
So I'll try to explain well what I'm trying to do. I have values in
column C in a spreadsheet that are pre-discount. Some items get a
discount and can then be used in finding out totals. But other values
in the sheet don't get discounted. So 2 types of prices.

B2 shows a base price of, say, $10, and C2 shows a discounted price of
$8 for this as it's elible for a 20% discount. D2 lists the # of
people being invited to this event, which is 25, which makes for $8
discounted price x 25 people which means a total cost for this
category of $200 in E2. I did this page manually and tailored the
formula for each scenario. For the prices that had no discount, I
manually put a formula in column E that would multiply the base price
by the # of people and disregarded the empty discount price cells in
column C and adjusted the formula to use the discounted price instead
when applicable.

But in order to make this spreadsheet "publishable", would rather have
a formula that does the calculation on either column without my
"fixing" anything manually.

I'm sure this is basic stuff but didn't find anything googling ... Pls
advise. Thanks! D

Oh, no, I'm an idiot! That's what I what I get for re-creating an
example spreadsheet at 11:30 p.m. after working a full day with
overtime and then running around on errands!

I'm sorry but I forgot I had an extra column to accommodate the
non-discount prices! B has original prices eligible for discount. C
has discounted prices. The missing column I just remembered to add to
re-created spreadsheet is column D which is for prices _not_ eligible
for a discount! D'uh. No wonder there was an issue for a threshold
number affecting outcome. In this case, your extra approach, Garry,
makes sense. Where to show what differentiates the two?!?

So the issue is to have the totals column, now moved over to column F,
reflect C2 discount x E2 # of people _OR_ D3 regular no-discount
possible price x E3 # of people, as the case might be. So totals
formula would hopefully do the following:

- multiply column C x column E
- OR multiply column D x column E
- but not accept values for both, meaning that there should be a value
in columnn C or column D but not in both

Sorry for confusion but I missed re-creating a full example in my
tiredness last night. Thanks so much!

 

[GS]

StargateFan has brought this to us :

Oh, no, I'm an idiot! That's what I what I get for re-creating an
example spreadsheet at 11:30 p.m. after working a full day with
overtime and then running around on errands!

I'm sorry but I forgot I had an extra column to accommodate the
non-discount prices! B has original prices eligible for discount. C
has discounted prices. The missing column I just remembered to add to
re-created spreadsheet is column D which is for prices _not_ eligible
for a discount! D'uh. No wonder there was an issue for a threshold
number affecting outcome. In this case, your extra approach, Garry,
makes sense. Where to show what differentiates the two?!?

So the issue is to have the totals column, now moved over to column F,
reflect C2 discount x E2 # of people _OR_ D3 regular no-discount
possible price x E3 # of people, as the case might be. So totals
formula would hopefully do the following:

- multiply column C x column E
- OR multiply column D x column E
- but not accept values for both, meaning that there should be a value
in columnn C or column D but not in both

Sorry for confusion but I missed re-creating a full example in my
tiredness last night. Thanks so much!

If you go with my revised formula for using only a discount percent
then you won't need the extra columns. If no discount applies then
leave the discount percent column empty, regardless of NumPeople
column. This allows you to have it both ways:

Case1:
Discount applies per x NumPeople;
Enter NumPeople AND discount percent.

Case2:
No discount applies regardless of NumPeople;
Enter NumPeople only.

This will allow you to use the same formula for both scenarios because
the discount will only be applied if the NumPeople is >= the minimum
criteria. Otherwise, both parts of the IF construct will return
BasePrice*NumPeople meaning discount percent will need to be entered
for it to be included in the calculation (if it applies).

 

[StargateFan]

StargateFan has brought this to us :

If you go with my revised formula for using only a discount percent
then you won't need the extra columns. If no discount applies then
leave the discount percent column empty, regardless of NumPeople
column. This allows you to have it both ways:

Case1:
Discount applies per x NumPeople;
Enter NumPeople AND discount percent.

Case2:
No discount applies regardless of NumPeople;
Enter NumPeople only.

This will allow you to use the same formula for both scenarios because
the discount will only be applied if the NumPeople is >= the minimum
criteria. Otherwise, both parts of the IF construct will return
BasePrice*NumPeople meaning discount percent will need to be entered
for it to be included in the calculation (if it applies).

I'm sorry. I've gotten all confused. We don't know the minimum # of
people here and there are just 25. I was just hoping to keep the
columns as is because this would mean re-writing the spreadsheet that
I already have at work. I have 2 columns already, one that shows
discounted pric and one that shows price that cannot have a discount
applied. I just needed to know how to get the formula already created
to accept either one column or the other but not both, whichever
happens to be filled.

My bad in that I forgot to bring the spreadsheet home with me. I now
have a permanent job, insofar as things are permanent these days <g>,
and it's private sector again rather than the usual govt contracts I
get. I don't feel I can use my USB stick like I always have and I now
only plug it in when needed, which is how I forgot the file to begin
with.

I'd greatly appreciate anyone's help in working with the scenario as
described. Believe me, I've kept the code above. It'll be great to
sit down and figure out how to apply this type of thing when the
scenario warrants it and the sole criteria is number of people.
However, here, only certain items are eligible so it feels best to
leave the spreadsheet as is as the column headers show that the
non-discount column deals with the other items that aren't discounted
no matter how many people we're dealing with.

Thanks so much. I appreciate all the help, as always! D

 

[GS]

In this case, then, instead of using DiscountPrice you could use
DiscountPercent only if it applies regardless of the number of
people...

=BasePrice*NumPeople*Discount

Otherwise, to persist using DiscountPrice...

=IF(DiscountPrice<>0,DiscountPrice,BasePrice)*NumPeople

...where DiscountPrice is only entered if it applies.