# Excel 2010 IF calculation statement

#### lostboyca

##### Board Regular
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%

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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:
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'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.

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.

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.

Got it to work I just removed the calculuation on the D column and it worked... Thanks a bunch.

Replies
5
Views
861
Replies
6
Views
268
Replies
1
Views
179
Replies
5
Views
201
Replies
4
Views
174

1,203,069
Messages
6,053,352
Members
444,655
Latest member
didr

### 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.

### Which adblocker are you using?

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

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