formulasgoogle sheets
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.
Something like this? The ranges are different here but the logic is the same - each cell in your range gets a formula like this (this is cell B2 in the screenshot):
=IF($E2=B$1,$A2,"")
Which checks to see if the value in the Name column is equal to the column heading of the current cell; if so, it adds the value you put in the HRS column, and if not, it stays blank.
I had a similar problem. sheetSpider does not appear to be available anymore. I tried writing my own script and it kinda worked but it was slow and didn't update reliably when the source data changed.
I eventually solved my problem with a pivot table (Data->Pivot table report...). Just select the range, then add all the columns you want to see as Rows (sounds counterintuitive I know) and set Filters for Column B > 1 and Column D = teacher's name. Create a new pivot table for each teacher on its own sheet where the second filter varies. More details about pivot tables in Google Sheets here. For me it is quick and updates well whenever the source data changes.
Best Answer