Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

If value is between ranges then answer is a specific number

Sample Sheet

I have a time sheet with start time, end time, and duration. The fee is based off the duration.

ie.
0-60min equals $100 61-75min equals $125 76-90min equals $150

I'm trying to figure out a way for it to automatically produce the fee off the duration cell.

Thank you for any help...very new to this,

I tried some IF/Then commands but it didn't seem to be the direction I need to go

My attempt include images per request:

This is the first sheet where the answer should display

You can see the =vlookup(F24,Fee!$A$2:$C$35,3,2) gives $0 although a time of 276 (F24) should equal $475

This is the Second sheet Fee! that data is being pulled from

I hope this helps more... Thank you for your time and effort on this, it's very much appreciated and I'm learning a lot from you all.

Solution

  • You need a VLOOKUP formula and a "rates" table that you can reference for your fees.

    Formula

    • =vlookup(D2,$G$2:$I$4,3,1)
      • where "D2" = minutes
      • where $G$2:$I$4 is the rates table

    Rates Table

    Rates

    • Take the duration,
      • convert it to minutes, and
      • VLOOKUP to find the rate that matches the number of duration minutes.

    EXAMPLE

    details

    • Duration:
      • Cell format: duration
      • formula: =B2-A2
    • Minutes: =hour(C2)*60+minute(C2)
    • Fee: =vlookup(D2,$G$2:$I$4,3,1)