Time, negative time, multiplication by numbers

[ajkgordon]

Hi. I have a table in which I want to calculate an adjusted amount based on time spent over a threshold. And it's giving me a headache with VALUE, SPILL and divide by zero errors. I think I read before you can't have negative time which could be casuing me issues but I also know you can substract one time from another as long as it doesn't end up with a negative.

So, this is what I have.

Cell B8 in 0% format, which is an adjustment rate.
Cell B9 in HH:MM format, which is a time threshold.

Column A "Billable time" in HH:MM format.
Column B "Billable amount" in 0.00 format.
Column C "Fixed price" in 0.00 format.
Column D "Charge" in 0.00 format.

The above values are simple values with no formulas.

In Column E "Adjusted charge", I would like to calculate a value in 0.00 format based on the above values.

If "Fixed price" has a value, then the result should be "Fixed price". Everything else can be ignored.
If "Fixed price" has no value or zero, then...
If "Billable time" is less than the time threshold in B9, then the result should be the "Charge".
If "Billable time" is above the threshold in B9, then the threshold should be subtracted from it, then the remainder adjusted by the adjustment rate in B8.
Finally the adjusted remainder, i.e. the time above the threshold, should be added back to the threshold.

It's basically calculating, or should be, a reduced rate if time spent is over a certain amount but only for that time over. The time before the threshold is charged at the nromal rate.

I've tried splitting out the formulas into separate columns but I'm hitting brick walls each time. Help would be hugely appreciated.

Thanks.

[DJunqueira]

It will be much easier to help you if you help us uploading a sheet.

[MrShorty]

I don't understand all of the calculations you are trying to describe, but the overall "decision tree" looks like this to me (formulas pseudocode just to illustrate the basic formula logic):

1) If the value in C is greater than 0, then just use the value in C. =IF(C>0,C,else1....)
2) Focusing on the "else1..." part of the formula, if the value in A is less than the value in C9, then just use the value in D. IF(A<$C$9,D,else2....)
3) If neither of the above conditions is met, then perform a more complex calculation involving times and rates and remainders and such (that I didn't understand).

The end result would be a nested IF() formula that will look something like =IF(C>0,C,IF(A<$C$9,D,else2....)). Once the else2 calculation is understood well enough to write it out in spreadsheet syntax, then place that formula into the else2 argument.

If I've understood, that's what I would expect to see. Does that help with the overall structure of the nested IF()?