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.

1

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

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

6

  • 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 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, ) – MARK MY ANSWER Jan 23 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 at 21:30
  • 1
    @Tera get rid off the FALSE with: =IF(ISNUMBER(B2), 800+(B2-1)/0.25*100, ) – MARK MY ANSWER Jan 23 at 21:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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