Ask Experts Questions for FREE Help !
Ask
    donf's Avatar
    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's Avatar
    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's Avatar
    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


    Name:  don.jpg
Views: 153
Size:  72.5 KB


    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's Avatar
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #5

    Feb 18, 2016, 11:03 AM
    Another fun one:

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

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.



View more questions Search