Value by hour

[amysong]

Hi there, first time poster...
I need help developing a table with 3 columns. The background here is I want to figure out how much someone has to pay by the hour for parking in a garage. The economy area costs $3/hour with a daily maximum of $28, and weekly maximum of $130. The "luxury" area costs $4/hour with a daily maximum of $35 and no weekly ceiling. So the table ultimately would look like:

Column A: hours 1-8760 (hours in a year)
Column B: economy area cost
Column C: luxury area cost

I have tried a bunch of different combinations of calculations and nested if statements but hit a wall every time. I would really appreciate if someone can help me figure this out!

Thanks,
Amy

 

[barry houdini]

Hello Amy, welcome to MrExcel

Do you mean you want a value for every number of hours from 1 to 8760? Are you assuming continuous parking? If so try this formula for B2

=INT(A2/168)*130+MIN(130,INT(MOD(A2,168)/24)*28+MIN(28,MOD(A2,24)*3))

and for C2

=INT(A2/24)*35+MIN(35,MOD(A2,24)*4)

 

[amysong]

Wow, that is brilliant! Thank you so much! This table will be used as a lookup so continuous is definitely the way to go. Thanks again. Incredible. Excel <3