Forum Discussion

GasDetect's avatar
GasDetect
Copper Contributor
Jun 17, 2024

Days Earned Calculator

Good Day!

 

I have a set of guidelines for earning bonus days off. (eg. between 251-450hrs earns 2 holiday days) As you can see in the attached pic. I would like to build a calculator that will allow me to enter a number of hours and it tells me how many bonus days I qualify for. The guidelines and rough calculator are in the pic, can you please help?

 

(Please note: these are not actual numbers, only made up values. Please don't call the labor board LOL)

excel
Formulas and Functions

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 17, 2024

    GasDetect 

    With a slightly different layout of the data it's a simple INDEX and MATCH formula as shown in the sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Jun 17, 2024

    GasDetect 

    You can achieve this easily with the XLOOKUP() function:

    =XLOOKUP(C3,A3:A12,B3:B12,,1)
or within a formula
=XLOOKUP(300,{200,450,700,950,1200,1450,1700,1950,2200,2450},{0,2,3,4,5,6,7,8,9,10},,1)

     

Resources