Google Sheet - Calculate a cost

Question

I'm trying to reproduce that lovely website that helps you calculate the cost of something you bought into my personnal google sheet so it's easier for me to use it.

I'm seeking here for help since I don't really know how to adapt the math when you change the value of year/month/day. As you may see, it's able to calculate the cost by year, but when you change the value to month for example, I don't know how to make it adjust the results.

I've tried =SUMIF, =IF, but I can't seem to find a clear way to do it. here is the doc

Thanks a lot!

Answer 1

I think what you are looking for is the SWITCH function:

You can in the cell D6 use the following formula:

=SWITCH(F2; I2; E1/E2; I3; E1*12/E2; I4; E1*52/E2; I5; E1*365/E2)

The logic is:

• check the cell F2 (where you have the dropdown)
• if the value of F2 equals I2 (Year) then, just divide the cost by the number of years
• if the value of F2 equals I3 (Month), then make E1*12 and divide it by the the E2 (same as (E1/E2)/12
• if the value of F2 equals I4 (Week), then calculate E1*52/E2 (same as above but with 52 weeks)
• if the value of F2 equals I5 (Day), then calculate E1*365/E2 (same as above but with 365 days)

And so on on the other cells, just change the differences between the formulas, between day, week, month and year.