Need Help With A Group of Nested Formulas

[artiststevens]

PLEASE SEE POST#8 FOR A BETTER UNDERSTANDING AND AN ATTACHMENT.

Hello,
I need help with making several nested formulas flow correctly. The formulas with more information are listed below.

This is the main one I’m having the problem with… I want it to be calculated were if either formula is True the result is V30850.

So If the value is greater than for the first formula proceed to the next formula, if it’s less than or equal to then the result is V30842 if not move onto the nested formula group.

Please Login or Register  to view this content.

(OR)

If the value is less than for the first formula proceed to the next formula, if it’s greater than or equal to then either the result is V30842 if not move onto the nested formula group.

Please Login or Register  to view this content.

(RESULT)
V30842


My attempt at combing the formulas:

Please Login or Register  to view this content.

Then combine the two formulas below with the previous formula so it can all reside in one cell.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanking you in advance for any and all help you can provide.

[FDibbins]

This looks very much like this thread...
http://www.excelforum.com/excel-form...-formulas.html

I will close this thread now, you can continue on the other 1. If they really are not the same, PM me and tell me how they differ, and I will re-open this

[FDibbins]

Thanks for the explanation, I will re-open this for you

[artiststevens]

Thanks for the explanation, I will re-open this for you

Thank you. :-)

[artiststevens]

I've attached a spreadsheet with a better explanation of what I'm trying to do. Any help would be greatly appreciated. Thank you.

[artiststevens]

Any further help would be greatly appreciated... Please see post #8

[FDibbins]

Perhaps this?

=IF(A10=IF($F$9<$D$9,MAX($D$9+$I$9,MAX($A$1,$A$9)),IF($F$9>$D$9,MIN($D$9+$I$9,MIN($A$1,$A$9)))),"",IF(A10>IF($F$9<$D$9,MAX($D$9+$I$9,MAX($A$1,$A$9)),IF($F$9>$D$9,MIN($D$9+$I$9,MIN($A$1,$A$9)))),IF($A$9<=$B$9,$F$9,""),IF($A$9>=$B$9,$F$9,"")))

All I did was add some absoluting for row 9, then plugged the formula in B into both of the B10 parts

[artiststevens]

Thank you FDibbins for the attempt. It's close to what I need but not quiet there. I attached another spreadsheet so you can see a better example with more information. Any further help you could provide would be greatly appreciated and I thank you very much for the help you already provided.

[FDibbins]

Thanks for the file

What is causing the switch from looking at row 9, to looking row 17? Part of teh problem (I think), was that I fixed the formula onto row 9, not knowing your table continued. So if I know when to change from 9 to 17 etc, we can probably put something better together.

Also, in your formula in B, you have this...
=IF(G9<E9,MAX(E9+J9,MAX(A1,A9)),IF(G9>E9,MIN(E9+J9,MIN(A1,A9))))
This is testing for >g and <g, what happens if they are =?
If that doesnt matter/wont happen, then that can be shortened to...
=IF(G9<E9,MAX(E9+J9,MAX(A1,A9)),MIN(E9+J9,MIN(A1,A9)))

Another 1 in C...
=IF(A10=B10,"",IF(A10>B10,IF(A9<=B9,G9,""),IF(A9>=B9,G9,"")))
A9>=B9 and A9<=B9 - both are looking at if A9=B9, which answer do you want here, they cannot both use the = part, the 1st will always trigger

[FDibbins]

OK, this is turning into a monster

I have automated the formula in B to change every 8 rows (that seems to be the pattern in your sample?) by using this in B10, copied down...
=IF(MOD(ROW()-2,8)=0,IF(G9<E9,MAX(E9+J9,MAX(A1,A9)),IF(G9>E9,MIN(E9+J9,MIN(A1,A9)))),B9)
If it is more or less than 8, adjust the bolded part as needed

Then, to automate the formula in C so you dont have to keep changing it (again, based on every 8th row), that grew to this mini-monster in C10, copied down...
=IF(A10=B10,"",IF(A10>B10,IF(INDIRECT("a"&ROW()-IF(MOD(ROW()-2,8)=0,MOD(ROW(),8)-1,ROW()-7))<=INDIRECT("b"&ROW()-IF(MOD(ROW()-2,8)=0,MOD(ROW(),8)-1,ROW()-7)),INDIRECT("g"&ROW()-IF(MOD(ROW()-2,8)=0,MOD(ROW(),8)-1,ROW()-7)),""),IF(INDIRECT("a"&ROW()-IF(MOD(ROW()-2,8)=0,MOD(ROW(),8)-1,ROW()-7))>=INDIRECT("b"&ROW()-IF(MOD(ROW()-2,8)=0,MOD(ROW(),8)-1,ROW()-7)),INDIRECT("g"&ROW()-IF(MOD(ROW()-2,8)=0,MOD(ROW(),8)-1,ROW()-7)),"")))

Now, when you try to combine those 2, as you requested, you end up with a full-blown monster that is a nightmare to edit and trouble-shoot.

Based on this, I would suggest that you leave the 3 columns there, and just hide the 1st 2

[artiststevens]

FDibbins,
First let me start off by saying that I greatly appreciate you helping me to solve this problem, it’s definitely turning into a monster. Lol! I should have mentioned the formula switches every 8 rows and examines the next set of 8.

I used the formulas you created and followed them according to your instructions and I get the result I need but that’s because the formula uses the values I manually entered. If I delete those manual entries the result is not what it should be.

But I think I’ve found a much simpler way of doing this.

The values in column “B” will always have a result while column “C” will be blank at times. Can you help with creating formula that goes something like this…

If B11 has a value but C11 doesn’t the result is B11 in D11
AND
If B11 has a value and C11 has a value as well the result is C11 in D11

I wanted to thank you again for everything. I can’t express my appreciation enough. Thank you.

See the newly attached sheet for more information.

[FDibbins]

If B11 has a value but C11 doesn’t the result is B11 in D11
AND
If B11 has a value and C11 has a value as well the result is C11 in D11

That part is simple
=if(C11="",B11,C11)

Back to the comment you made about

I get the result I need but that’s because the formula uses the values I manually entered

Which values are these, and which formula were you refering to? (I know the mini-monster wasnt completely finished)

[artiststevens]

That did it! Thank you very much! I decided that instead of trying to combine them just create a formula that chooses between the two whenever C is a value. I appreciate all of your help and patience.

Now for the mini-monster… when I first typed my response I said it was based on my manual entries and what I should have said was based on the formula entries and that the changed didn’t need to take place in B or C but in D. The goal of D was to eliminate C and B. C takes priority over B so if C is true the result is C. The problem with the mini-monster was that B and C are dependent on the past result of D. If D doesn’t contain a result then there is no Value to be entered. I attached a new workbook with the formulas you first provided to show you what I mean.

Sheet1 is where are entered the min-monster formulas as per your instructions and Sheet2 contains the formula that chooses between B and C based on which has a value. C takes priority over B. So that’s why my original goal was to combine the two in order to cut back on the number of rows. But it was too complicated to get it to work, so I decided on if C is blank or then check the formula in column B and that would be the result. Review C first if not C then B. I hope that makes it a little clearer.

It may have more rows but I just appreciate you helping me to solve the problem. Thank you again very much!

[FDibbins]

Im not sure if you were asking more questions here, or showing me what you ended up with? (sorry, my head is going in a few different directions right now lol)

[artiststevens]

It's fine. Lol! I was trying to give you a better understanding based on the questions you asked. Thank you again.

[FDibbins]

OK just checking, I thought that was the case, but did not want to assume.

Thank you for the kind words and the feedback, always appreciated