Forum Discussion

Norman Zanders's avatar
Norman Zanders
Copper Contributor
Feb 25, 2018

Help with creating an "IF" formula

I need help in creating an IF formula. I am trying to create a formula that will populate the cell with the amount of Trip Pay is due to a traveler based on the number of miles that is entered in the...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 25, 2018

Hi Norman,

 

Actually your IFs give the answer if convert to nested IF. The only, the logic is bit unclear

- what shall be if A1 is less than 26;

- last "if" is in conflict with previous ones, all of them are for A1 < 101. Perhaps >101?

Formula looks like

=A1*0.45+IF(A1<26,0,IF(A1<51,77.5,IF(A1<76,89.25,IF(A1<101,101,54))))

 

  • Norman Zanders's avatar
    Norman Zanders
    Copper Contributor
    Feb 25, 2018

    Thanks for your help. To answer your question, there would never be a time that the number in A1 would be less than 26. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 25, 2018

      So we may keep zero for that

       

      • Norman Zanders's avatar
        Norman Zanders
        Copper Contributor
        Feb 27, 2018

        I had to change my formula to VLOOKUP and look up from a table. I know how to get the formula to work. I have a DROPDOWN list in C5 where you select a City from the 1st column of the table. The VLOOKUP is in D5 and populates the cell with the Mileage from the table that is assigned to the City.

         

        The only problem I am having now is if C5 is blank (not used), then in D5 has #N/A displayed in D5. Is there a way to accomplish this action without #N/A being displayed if C5 is BLANK?