kind of complicated calculations....i'm stuck :-(

[j33pguy]

hi,
i have the following table which needs to figure out costs for going from a level to another level. the cost of each level is shown in column D. in column E you see if that level is "incremental" or "not incremental".
if a level IS incremental (Y), that means that the cost of going from one level to the next is the difference between the cost of the two levels.
if a level is None incremental (N) then the cost of going from one level to the next is the total cost of the new level.

Book1
	D	E	F	G	H	I	J	K
4		Incremental	Level	0	1	2	3	4
5	$40	N	4
6	$30	N	3
7	$20	Y	2
8	$10	N	1
9	$0		0
Sheet1

G4:K4 show the level i'm starting with (hypathatically)
i'm trying to fill G5:K9 (only the white parts cause you can only go UP in levels)

 

[j33pguy]

as an example, with the way that it's set up now:
going from 0 to I , it would cost 10 dollars because level I is Not incremental.
going from I to III, it would cost 30 dollars
going from I to II wold cost only 10 dollars because II is incremental.
going from I to IV would cost 70 because from I, i'd have to buy level III first for 30, then buy IV in its entirety for another 40.
going from II to III would cost 30.


i hope this helps.
i'm trying to use IF statements to do this but it's very complicated.

 

[j33pguy]

i wrote the formulas for going from 0 to 0, 0 to I, 0 to II and 0 to III which would go into cells G9, G8, G7, G6

=0

=D8

=IF(AND(E7="N",E8="N"),D7+D8,IF(AND(E7="N",E8="Y"),D7,IF(AND(E7="Y",E8="N"),D8+(D7-D8),IF(AND(E7="Y",E8="Y"),D7,FALSE))))

=IF(AND(E6="N",E7="N",E8="N"),D6+D7+D8,IF(AND(E6="N",E7="N",E8="Y"),D7+D6,IF(AND(E6="N",E7="Y",E8="Y"),D6,IF(AND(E6="N",E7="Y",E8="N"),D8+D6,IF(AND(E6="Y",E7="Y",E8="Y"),D6,IF(AND(E6="Y",E7="N",E8="Y"),D7+(D6-D7),IF(AND(E6="Y",E7="Y",E8="N"),D8+(D6-D8),(D8+D7+(D6-D7)))))))))

i can't get the one that would go into G5

 

[just_jon]

Does this produce the desired results?

Book2
	D	E	F	G	H
5	$ 40.00	N	4	Start	Stop
6	$ 30.00	N	3	0	2
7	$ 20.00	Y	2	Cost=	$ 20.00
8	$ 10.00	N	1
9	$ -		0
10
Sheet4

 

[j33pguy]

SO VERY CLOSE!!!!!
i'm getting the incorrect number IF there is a "Y" in E6 and i'm going from II to IV
the number shows:225
BUT it should show ONLY 100!!! because i don't need to go to level 3 first! i can just get the difference between IV and II directly without going through III
now if E6 WAS "N", then yes, i would need to buy level III first, then buy level IV.

also, how can i duplicate the table that i had initially using your formula? or is that not possible?

 

[just_jon]

How about posting your sample data as it is now?

 

[just_jon]

Given the sample I posted before, what are the expected results for:

o to 4
0 to 3
0 to 2

1 to 2
1 to 3

2 to 4

 

[j33pguy]

Given the sample you posted before:

0 to 4 should be 40

0 to 3 should be 30
0 to 2 should be 10+(20-10)=20

1 to 2 should be 20-10=10
1 to 3 should be 30

2 to 4 should be 40

would you still like me to post the table i have with the half working formulas?

 

[just_jon]

t178713.xls
	D	E	F	G	H
5	$ 40.00	N	4	Start	Stop
6	$ 30.00	N	3	2	4
7	$ 20.00	Y	2	Cost=	$ 70.00
8	$ 10.00	N	1
9	$ -		0
Sheet4

Still not convinced this is a good solution...

 

[just_jon]

from -- http://www.mrexcel.com/board2/viewtopic.php?t=178887

hi...i'm trying to compare 4 cells to a value....
the following is what i've got so far...but it doesn't give the correct output.
if cells G5 through G8 are equal and are equal to "Y", i'd like to return "yes", otherwise "NO"

what i have and what doesn't work properly is:

=if(AND(G5=G6=G7=G8="Y"),"Yes","No")

Suggestions?

Probably should stick with the original post


=AND(G5="Y",G6="Y",G7="Y",G8="Y"),"Yes","No")