Increase Price based on time

[uwfenske]

I need a solution for a formula to insert into Column J for the Adjusted Price cells.
It need to add on the $10.00 (O2) to the Base Price in Column I for every 10 minutes over 4 hours.
I entered sample values in column J for reference.

I have been using nested IF statements, but at a point where I am "using more levels of nesting that the file format allows". I am using the Excel Workbook format.

 

[Eric W]

Try:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1														Added Price	Over 4 hours
2															$ 10.00
3
4
5								Hours	Base Price	Adjusted Price
6				Trip 1				3.5	$ 100.00	$ 100.00
7				Trip 2				4.17	$ 100.00	$ 110.00
8				Trip 3				4.33	$ 100.00	$ 110.00
9				Trip 4				4.5	$ 100.00	$ 130.00
10				Trip 5				4.67	$ 100.00	$ 140.00
11				Trip 6				4.84	$ 100.00	$ 150.00
12				Trip 7				5	$ 100.00	$ 160.00
13				Trip 8				5.17	$ 100.00	$ 170.00
14				Trip 9				5.33	$ 100.00	$ 170.00
15				Trip 10				5.5	$ 100.00	$ 190.00
16				Trip 11				5.67	$ 100.00	$ 200.00
17				Trip 12				5.84	$ 100.00	$ 210.00
Sheet10

Cell Formulas
Range	Formula
J6:J17	J6	=I6+INT(MAX(H6-4,0)*6)*$O$2

Edit: I just noticed that row 14 has a value of $170, when you expect $180. This is due to rounding. This formula might work a bit better for you:

=I6+INT(MAX(H6-4,0)*6+0.05)*$O$2

 

[uwfenske]

Eric,
This seems to be on the right track. I did not explain quite accurately.
How would I adjust the formula so that if the time is over 4 hours and up to the 4.17 hours, it adds the extra $10. For instance, if the time is 4.03, it would be $110, then at 4.17 it is also $110, but then at 4.33 it goes to $120.

As an example

 

[johnnyL]

Edit: I just noticed that row 14 has a value of $170, when you expect $180. This is due to rounding. This formula might work a bit better for you:

@Eric W. I saw that and thought that also, but then I thought about it. .33 is slightly less than 1/3 and therefor I would consider your original formula to be accurate.

 

[Eric W]

Then how about:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1														Added Price	Over 4 hours
2															$ 10.00
3
4
5								Hours	Base Price	Adjusted Price
6				Trip 1				3.5	$ 100.00	$ 100.00
7				Trip 2				4.03	$ 100.00	$ 110.00
8				Trip 3				4.16	$ 100.00	$ 110.00
9				Trip 4				4.33	$ 100.00	$ 120.00
10				Trip 5				4.5	$ 100.00	$ 130.00
11				Trip 6				4.67	$ 100.00	$ 150.00
Sheet10

Cell Formulas
Range	Formula
J6:J11	J6	=I6+ROUNDUP(MAX(H6-4,0)*6,0)*$O$2

Note that this is still susceptible to rounding. I changed the 4.17 to 4.16, since .17 is slightly over 10 minutes and triggered the formula to add another $10.

 

[uwfenske]

Eric and johnnyL,
Thank you very much for the help. That ROUNDUP(MAX) is perfect. Thank you so much

 

[Eric W]

Glad we could help! Thanks for the update.