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

#### j33pguy

##### Well-known Member
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
DEFGHIJK
4IncrementalLevel01234
5\$40N4
6\$30N3
7\$20Y2
8\$10N1
9\$00
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)

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.

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

Does this produce the desired results?
Book2
DEFGH
5\$ 40.00N4StartStop
6\$ 30.00N302
7\$ 20.00Y2Cost=\$ 20.00
8\$ 10.00N1
9\$ -0
10
Sheet4

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?

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

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?

t178713.xls
DEFGH
5\$ 40.00N4StartStop
6\$ 30.00N324
7\$ 20.00Y2Cost=\$ 70.00
8\$ 10.00N1
9\$ -0
Sheet4

Still not convinced this is a good solution...

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

j33pguy said:
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")

Replies
2
Views
992
Replies
0
Views
425
Replies
4
Views
222
Replies
1
Views
4K
Replies
25
Views
801

1,196,048
Messages
6,013,096
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back