Excel 2010 IF calculation statement

lostboyca

Board Regular
Joined
Nov 2, 2005
Messages
62
Hello members,

I've been scratching my head for the past week to try to do this calculation

I have the following cells
D E F G
Pts Status Type End Pt

For D (Pts). I will have the following numbers
100
200
300
400

For E (Status)
W
L
H
P
V

For F (Type)
S
P

For G (End Pts) This is the final calcuation with the following scenarios

For G (End Pts) This is the final calcuation with the following scenarios

If
- D = 100, E = W, F = S, then G should = D
- D = 100, E = L, F = S, then G should be D*-110%
- D = 100, E = P, F = S or P, then G should be 0
- D = 100, E = H, F = P (Only scenario), then G should be D*90%
- D = 100, E = W, F = P, then G should = D
- D = 100, E = W, F = P, tehn G should = D*250%

thanks in advance
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
With this many scenarios, I wouldn't try to use an IF statement. Instead, I would build a table of options and use a VLOOKUP function. (There may be better options but I personally like using the VLOOKUP whenever possible.)

In my example, I built a table with all 3 cells together. (such as 100 + W + S = 100WS) For each entry in the table, I add a multiplier to use for that particular situation in the second column. The formula combines the cells in D, E, and F for a given row in the data set and looks that up in the table to obtain a multiplier. This figure gets multiplied by whatever is in column D. If a particular combination doesn't exist, then it returns a blank. You could modify the blank in the formula to say "No Match" or whatever you choose.

A word of caution.... This approach could present problems with certain types of data in the table that might overlap and confuse the formula but in your case it should work just fine. (Example 3 columns with A - BB - CCC would equal the same as AB - BC - CC when you combine those fields even though the data in each column is different. Just keep this in mind if you use different types of values.)

Also, in your example IF statements, the last 2 have the same data in cells D, E, and F but give different results. I assume this is a typo.

I also should mention that the VLOOKUP formula will find the first match in the table so you want to be sure and not enter any duplicate entries within the table. (Such as you would not want to enter 100WS twice in the table.) In fact, you really wouldn't want to do that intentionally anyway because each unique lookup value in the table should only have one multiplier.)

Hope this helps.
Book1
DEFG
1PtsStatusTypeEndPts
2100WS100
3100LS-110
4100PS0
5100PP0
6100HP90
7100WP100
8200WP 
9300WS 
10400WP 
11100VS 
12
13
14
15Table
16100WS1
17100LS-110%
18100PS0
19100PP0
20100HP90%
21100WP1
Sheet1
 
Last edited:
Upvote 0
I've notice in your Table you had only 100. D column can range from 100-5000. Will that table work and vlookup work in that manner?
 
Upvote 0
I've notice in your Table you had only 100. D column can range from 100-5000. Will that table work and vlookup work in that manner?

I only included 100 in my table because that is all you provided in your IF scenarios. The Vlookup formula will work for as many things as you want to put in the table. However, after your last post it appears that your original post didn't tell the full story of what calculations you need to make.
For example, you indicated in your original post that you had 100, 200, 300, and 400 for column D but only gave IF examples if column D was equal to 100.
Now you are saying column D can be from 100 to 5,000 but you still do not provide enough detail on how you want the calculations to work.

In trying to read between the lines, it seems that column D may not really even need to be part of the lookup but since you are unclear on what your really need, I can only speculate.

Vlookup may or may not be the best option but I will need more info before I can determine that. If you can better explain what you really need and provide more specifics, I can better assist.
 
Upvote 0
Sorry for the confusion. Column D is something the user will have to input and it the calculation will depend on whatever number the user inputs.
 
Upvote 0
It sounds like the user input in column D needs to be taken out of the table so you would only have 2 items in the table to determine the calculation rules. I still don't think you have fully explained all of the options, combinations for column E and F. I can try again if you can fully explain how you want it to work in all situations.
 
Upvote 0

Forum statistics

Threads
1,223,493
Messages
6,172,602
Members
452,466
Latest member
Lynlindsay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top