The formula got reconstructed somehow when you pasted. Following is the
result of direct substitution of TC with P95 and AC with P96. I believe you
also want to substitute the J98:J101 values in place of the hard numbers.
I'll leave that to you. I suggest you test the formula before pasting the
J98:J101 values.

=If(P96 <= P95, P96 * 0.442, If(P96 <= P95 + 100, P95 * 0.442 + (P96 - P95)
* 1.327, If(P96 <= P95 + 1500, P95 * 0.442 + 100 * 1.327 + (P96 - P95 - 100)
* 1.098, P95 * 0.442 + 100 * 1.327 + 1400 * 1.098 + (P96 - P95 - 1500) *
0.9255)))

Regards,
Greg

"Daniel Q." wrote:

Greg - thank you very much for your help so far. My mind keeps juggling old
C++ syntax (if..., then...)

Your assumption on P95 being theoretical consumption is correct.

Seeing your formula makes sense. I've followed the IF logic but i think i'm
missing some parentheses somewhere.

I replaced actual consumption(AC) with P96 and theoretical (TC) with P95

This is what i copied into the cell:

=IF(P96<=P95,P96*0.442,(IF(P96<=P95+100,P95*0.442+ (P96-P95)*1.327),(IF(P96<=P95+1500,P95*.442+100*1.327+( P96-P95-100)*1.098),P95*.442+100*1.327+1400*1.098+(P96-P95-1500)*.9225)))

DQ

"Greg Wilson" wrote:

Daniel,

I probably don't have my interpretation right but this is at least a stab at
it. This seems to comply with your verbal description (more or less) but
contrasts with your formula. Hopefully this will at least serve as a good
start.

Interpretation:-

Cells J98:J101 contain the constant values as follows:
J98 = 0.442
J99 = 1.327
J100 = 1.098
J101 = 0.9255

- Cell P94 contains any given date (June 06 in your example)
- Cell P95 calculates the theoretical monthly consumption (TC) by
calculating how many days are in the month specified in P94 and multiplying
by the average daily unit volume of 677.3 (or just calculates the number of
days in the month ???)
- Cell P96 contains the actual monthly consumption (AC)
- Cell P97 contains the formula

Logic foundation is as follows:

If AC<=TC then: AC * 0.442
ElseIf AC<=TC + 100 then: TC * 0.442 + (AC - TC) * 1.327
ElseIf AC<=TC + 1500 then: TC * 0.442 + 100 * 1.327 + (AC - TC - 100) *
1.098
ElseIF ACTC + 1500 then: TC * 0.442 + 100 * 1.327 + 1400 * 1.098 + (AC -
TC - 1500) * 0.9255

whe-
TC = Theoretical Consumption (or P95 value ???)
AC = Actual Consumption or P96 value

Suggested formula:

=If(AC <= TC, AC * 0.442, If(AC <= TC + 100, TC * 0.442 + (AC - TC) * 1.327,
If(AC <= TC + 1500, TC * 0.442 + 100 * 1.327 + (AC - TC - 100) * 1.098, TC *
0.442 + 100 * 1.327 + 1400 * 1.098 + (AC - TC - 1500) * 0.9255)))
The formula requires substitutions for AC and TC and the J98:J101 cell
references in place of the hard numbers in the formula. This all should be
easy. Sorting out what you want is the challenge. *The formula was written in
a hurry and is not tested*.

Again, I know this is very likely wrong but hope the comparison will help
illuminate. In particular, I don't know if P95 contains the theoretical
consumption (days in month x 677.3 or just the days in the month). You also
appear to be wanting to illiminate refering to the value in P95 but coding
its contents into the formula instead. If so, it should be an easy copy/paste
into the formula once this is clarified. You might also want to consider
going with named ranges in place of cell references so that you can actually
use AC and TC in your formula.

I have to go out for several hours and won't be able to respond until this
evening.

Regards,
Greg