 Ask Remember Me? donf Posts: 5,679, Reputation: 582 Printers & Electronics Expert #1 Feb 6, 2016, 08:38 PM
If/Else
Is it possible to do an IF, Then, Else test?

I have a column that can have the digits 1,2,or 3 in a cell.

If the value is 1, I want to divide the value of an adjacent cell by 120.
If the value is 2, I want to divide the value of the adjacent cell by 208.
If the value is 3, I want to divide the value of the adjacent cell by 360.

How can I do this. So far the best I can do using the "IF" function is to make one test and adjust for two possible numbers. ScottGem Posts: 64,970, Reputation: 6056 Computer Expert and Renaissance Man #2 Feb 7, 2016, 01:52 PM
I would the following in a cell off to the side (Z2?)

=IF(B2=1,120,IF(B2=2,208,350))

Then in the adjacent cell use:

=C2/Z2 WallyHelps Posts: 1,011, Reputation: 136 Ultra Member #3 Feb 8, 2016, 07:06 AM
A more flexible (though slightly more complex) solution
Hi Don!
Here's another method that gives you some flexibility in case you need to change something in the calculation. It uses the Visual Basic capabilities in Excel.

I'm just getting my feet wet in this VBA stuff (doing it for tax calculations), but this example should get you started.

Assuming data is in columns A and B, with the result to go into column C.

1. Go to the Visual Basic Editor (hit Alt-F11)
2. You want to Insert->Module to get a blank sheet for your VBA program
3. Here is my quickie code You may want to copy and paste it into the VBA editor:

Code:
```Function DonFunc(InputOperation As Integer, RawData As Double)
'We expect a 1,2,or 3 as input operation
'The value to perform the operation on is the second parameter

Select Case InputOperation
Case 1
DonFunc = RawData / 120
Case 2
DonFunc = RawData / 208
Case 3
DonFunc = RawData / 360
End Select
End Function```
4. Click on File->Close and return to Excel (it seems to be saved automatically)
5. In your cell C1 put the formula: =DonFunc(\$A1, \$B1)
6. Copy this cell to the remaining cells in column C If you need to alter the calculations, just go back into the VBA editor with Alt-F11 and make the changes. Your spreadsheet shouldn't have to change.

Hope this is fairly self-explanatory.
Good luck!
WallyH donf Posts: 5,679, Reputation: 582 Printers & Electronics Expert #4 Feb 8, 2016, 03:39 PM
Scott - Wally,

I got it to work using a nested IF formula. My original construct was wrong.

Here is what I finally got to work:

=IF(K10=1,L10/120,IF(K10=2,L10/208,L10/360)) JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #5 Feb 18, 2016, 11:03 AM
Another fun one:

=L10 / CHOOSE(K10, 120, 208, 360)

 Question Tools Search this Question Show Printable Version Email this Page Search this Question: Advanced Search