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.

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

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

Attachment 48208


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

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))

Another fun one:

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