Simplifying a max(if-then) equation...

Hello,

My equation is getting longer every time I need to insert columns, and add them to the equation. Is there a way to make the equation below general? Basically, I need the max of a bunch of cells only if the cell 2 columns away from each, respectively, is not zero:

={MAX(IF(M13<>0,K13),IF(P13<>0,N13),IF(S13<>0,Q13),IF(V13<>0,T13),IF(Y13<>0,W13),IF(AB13<>0,Z13),IF(AE13<>0,AC13),IF(AH13<>0,AF13),IF(AK13<>0,AI13),IF(AN13<>0,AL13),IF(AQ13<>0,AO13),IF(AT13<>0,AR13),IF(AW13<>0,AU13),IF(AZ13<>0,AX13),IF(BC13<>0,BA13),IF(BF13<>0,BD13),IF(BI13<>0,BG13),IF(BL13<>0,BJ13),IF(BO13<>0,BM13),IF(BR13<>0,BP13),IF(BU13<>0,BS13),IF(BX13<>0,BV13),IF(CA13<>0,BY13),IF(CD13<>0,CB13),IF(CG13<>0,CE13))}

Would be nice not to edit this stinker ever again. Maybe searching a range out to the right end of the sheet

Thanks.

Dave

Yep. If any of those is 0, exclude from max. And your formula works. Not sure how I made sure that the M-Y range starts/ends on a yellow "condition", and the K-W range starts/ends on a "value". I assume it won't work otherwise.

And if I need to insert columns, no editing. If I need to add columns on the end of the range, small easy edit. No worries.

Where's your tip cup

Thanks!

Or try this ...

=MAX(IF((M1:Y1="condition")*(M13:Y13<>0),K13:W13,""))

Enter with Ctrl+Shift+Enter.

Interesting. I'll go with the first solution that doesn't require extra text somewhere. I'm liable to forget it's needed and delete it

Oh oh, spoke too soon. I needed to move the equation to a new cell (to H13 from H14), and I get a circular reference now. I cannot attach the sample.

Maybe I can describe:

In H13 I want your: ={MAX(IF(MOD(COLUMN(M13:CG13),3)=1,IF(M13:CG13<>0,IF(MOD(COLUMN(K13:CE13),3)=2,K13:CE13))))}

In I13 is: =INDIRECT("R"&ROW()&"C"&(MATCH(H13,K13:CG13,0)+COLUMN(J:J)+2),FALSE)

In J13 is: =INDIRECT("R9"&"C"&(MATCH(H13,K13:CG13,0)+COLUMN(J:J)),FALSE)

Then comes the data on the same row in K+: "value" then a number then the "condition" we spoke of. Repeating like that.

Can I get this sample sheet to you some way? The attachments button seems not to do anything...

OK, I did upload Book3, but I am not sure it's attached to this post.

There are links inside, so don't update, and the values should show.

Trying to put the equation in H14 into cell H13.

Let me know if you don't see a circular reference message.

L13 refers to H13 ("h" inL8) so you get circular reference when referencing K13 onwards

I see, and your 2nd solution didn't work when placed there either, since they both refer to the whole range. Not sure what the solution is. I would have thought the Indirect masks the circularity.

Instead of CH I used G13, but still a circular reference because the formula (G/H13) references the range containing formula like in L13, which in turn references G/H13.

Hi Spacely,

I am just throwing out an idea here that I am not super familiar with, but it may help. If you change you the calculations to iterative, then I think you would avoid the circular reference error. Hopefully someone else can better expand on this approach or suggest something better.

Without a macro:
file→options→formulas→click enable iterative calculation checkbox
You would have to manually change it between calculation types each time you open the workbook I believe

With a macro (see attachment):

Workbook_Open():

Please Login or Register  to view this content.

Workbook_BeforeClose:

Please Login or Register  to view this content.

Ignore the circular reference when opening the attachment


DMG

Seems the couple macros work to suppress the message. Seems pretty fast, so the circularity must not have confused it too bad. Anything bad about simply keeping the Iterative Calculation on for all books, always?