1

I am working with a fee sheet that goes up in $ increments based on time and I would like Google Sheets to do the math to calculate the cost based on the time entered into a cell.

Example: a time value of 1 entered into a cell will always equal $800. Every additional 15 mins adds an additional $100 to the original $800. i.e.- 1 hour = $800, 1.25 hrs=$900, 1.5 hrs=$1000...

I would like to enter the time in one cell and have the sheet calculate the cost into another cell.

Improve this question

1 Answer 1

Reset to default
1

BASE + (TIME - BASE) / 15min * EXTRA$

=800+(A4-1)/0.25*100

6

Improve this answer
4
  • 1
    Thanks again for answering another one of my questions! You've been most helpful today!Out of curiosity, is there a reason why the calculated value stays at 400 if the "time" cell is empty? I will be working off of a blank sheet for each patient (this is calculating surgical fees based on time), and entering the time as needed, but I noticed that when the time field is blank, the "total" field stays at 400. Not a big deal, I can live with it, just curious.
    – Tera
    Jan 23, 2019 at 20:52
  • 1
    its because there is no boundary restriction. you can fix it like: =IF(A2>=1, 800+(A2-1)/0.25*100, )
    – user0
    Jan 23, 2019 at 20:58
  • 1
    Yes, I was actually able to fix it myself like:=IF(ISNUMBER(B2),800+(B2-1)/0.25*100)as the cell will either have a number (never less than 1) or not. Looks like it works, when the "time" cell is empty, the "total" cell now says "FALSE"
    – Tera
    Jan 23, 2019 at 21:30
  • 1
    @Tera get rid off the FALSE with: =IF(ISNUMBER(B2), 800+(B2-1)/0.25*100, )
    – user0
    Jan 23, 2019 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.