Forum Discussion

Lin Wang's avatar
Lin Wang
Copper Contributor
Mar 10, 2017
Solved

Data/What If/Data Table Problems

Hi I'm trying to set up a children attendance and payment work sheet.  I need help as to how to set up a formular to calculate payment depending on number of children attending.  So basically if cell E2 is 0 = 0, if cell E2 is 1 = 4, if cell E2 is 2= 6, if cell E2 is 3 or more = 8. How do I put this into a formula. Thank you for your help.

 

Formulas & Functions
Need Help
  • Haytham Amairah's avatar
    Haytham Amairah
    Mar 10, 2017

    Hi Lin,

     

    Simply, you have to use somthing like this:

     

    =IF(NOT(ISNUMBER(E2)),"",
IF(E2=0,0,
IF(E2=1,4,
IF(E2=2,6,
IF(E2>=3,8,
)))))
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Mar 10, 2017

    Hi Lin

     

    I like Haytham Amairah's solution.

     

    Here's my take using a vlookup or safer still an INDEX MATCH

     

    There's no benefit over Haytham's solution in your particular scenario so I'd stick with that.  However if you want to add more combinations in the future this may be easier to manage.

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Mar 10, 2017

    Hi Lin,

     

    Simply, you have to use somthing like this:

     

    =IF(NOT(ISNUMBER(E2)),"",
IF(E2=0,0,
IF(E2=1,4,
IF(E2=2,6,
IF(E2>=3,8,
)))))

Resources