embedded ifs

[Daniel Q.]

i asked a question like this last week but i did not have all the info on how
my ifs statements were going to work but now i do so hopefully y'all can help
me out again. I want to keep the info in one cell so i understand this will
be a pretty long one.

First a cell, P94, shows June-06. P95 recognizes how many days are in the
month - =DAY(DATE(YEAR(P94),MONTH(P94)+1,0).

I have an average base volume of units that may or may not be consumed every
day (677.3).

P94 = June 06
P95 = =DAY(DATE(YEAR(P94),MONTH(P94)+1,0)*677.3
P96 = actual consumption

P97 should contain the IF statement that says if P96 is less than or equal
to P5 then that volume should be multiplied by .442, if it is greater than
P95 but less than or equal to P95+100 then it should be P95 x .442 + extra
volume x 1.327. If P96, the actual consumption, is greater than P95+100 but
less than or equal to P95+1500 then it should read P95 x .442 +100 x1.327 +
extra volume x 1.098. And finally, if P96 is greater than P95 + 1500 then
the outcome should be P95 x .442 + 100 x 1.327 + 1400 x 1.098 + extra volume
x .9255.

I know this one is long, and for me, pretty difficult. I started working on
it but i just can't fathom how to do it.

So far i've come started with this -

=IF(AND(P95>(DAY(DATE(YEAR(P94),MONTH(P94)+1,0))*677.3),(P95>(DAY(DATE(YEAR(P94),MONTH(P94)+1,0))*677.3+1500))),(DAY(DATE(YEAR(P94),MONTH(P94)+1,0))*677.3*J98)+(100*J99)+1400*J100+(P95-(DAY(DATE(YEAR(P94),MONTH(P94)+1,0))*677.3+1500))*J101)

- the "J's" being where the .442,1.327,1.098 and .9255 are located (J98-J101).
Plus the problem with this is that this only works if P95 is always larger
than P95+1500

Thank you for all your help!

D.

 

[Greg Wilson]

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 AC>TC + 1500 then: TC * 0.442 + 100 * 1.327 + 1400 * 1.098 + (AC -
TC - 1500) * 0.9255

where:-
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

 

[Daniel Q.]

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]

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.]

Perfect!! Thank you very much!

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