Value by hour

amysong

New Member
Joined
Nov 10, 2011
Messages
13
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Nov 10, 2011
Messages
13
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,658
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top