Excel with mulitple criteria 1

[pandabear1]

I have an Excel spreadsheet with a column that has different discount categories,
i.e. A,B,C,D and E. Each one of the categories has a discount attached to it.
A is muliplied by .50 as an example
B is mulitplied by.60
C is muliplied by .70
D is muliplied by .80
E is muliplied by .90
The categories are in column B and the value they are discounted from are in column F.
So what I need is a formula that states if column B has an "A" value mulitply it by .50, but if column "B" has a "B" mulitply by .60 and so on. I have inserted a column G to represent the finished value

 

[SkipVought]

Hi,

The simplest method is to use a lookup table.
[tt]
Category Discount
A .5
B .6
C .7
D .8
E .9
[/tt]
Use Named Ranges, based on the HEADINGS in the lookup table example
[tt]
g2: =INDEX(Discount,MATCH(B2,Category,0),1)
[/tt]
This is much cleaner than using 5 nested IF statements and is much easier to maintain, if and when the discount schedule changes.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[pandabear1]

I labeled column B as "category" and then on another sheet I labeled the cells with the disounts in "discount". But I don't understand how the formula looks at the price column in this case column F and then looks at column B to see what category it is A, B, C etc and then applies the appropriate discount from the discount column on the other worksheet.
Column B could be A for one item in the pricelist and then when you go to another item column B could be a C. Each item has a category assigned to it and therefore a discount factor.
thanks

 

[SkipVought]

My lookup table is in 2 columns on the same sheet. Could be a different sheet than your data is on. It is a reference table. I suggest putting the lookup table on a separate sheet.

Just adding headings, does not make a named range. Here's what to do.
[tt]
Select ALL the cells in the lookup table INCLUDING the heading names

Insert > Name > Create - Create Names in TOP row
[/tt]
This process creates the named ranges. Now your're ready to start entering formulas on your sheet containing the data in column B.

On the sheet with your data, enter the formula that I posted, making sure that the column B reference is for the proper row.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[pandabear1]

I think I did the labeling correctly but I did a copy and pasted your formula into the first cell in this case G8 as the first category is in B8. What I ended up with was the first discount form the second worksheet cell G8 is .5
second worksheet looks like this column A:
Discount
.5
.6
.7
.8

 

[SkipVought]

second worksheet looks like this column A:"

Where is the other column in the Lookup Table???

Your SECOND sheet ought to have these TWO columns, with Named Ranges!!! Was this not perfectly clear?
[tt]
Category Discount
A .5
B .6
C .7
D .8
E .9
[/tt]



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[pandabear1]

I have done as you said for the second sheet and what comes up is the discount value only i.e. .5 I don't see in the formula where the category discount value is multiplied to the value that is in column F in my case:

Column B Column F Column G
A 10000 discounted value which would be 1000*discount A from second sheet.
I think we are close just have to get the column F into the factor I think.

 

[SkipVought]

My formula only adressed the lookup of the Discount. All you have to do is complete the formula with the cell reference to the column F value. Excel 101. Have you never done any Excel formulas before?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[pandabear1]

that worked, just wanted to confirm. Thanks for all your time and patience.

 

[SkipVought]

Trying something new or different, will not break your computer.

Glad you figured it out!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}