Simplifying max(if(A=B*C,B ... ) equation.

Sorry for the messy equation, but I think there's a way to simplify this so it doesn't keep growing everytime I add more columns. Perhaps something with ranges...

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

Not worried about the ? check at the end.

Thanks a bunch.
Dave

try

=INDEX(K13:CH13,SMALL(IF((K13:CH13)*(M13:CJ13)=CR13,COLUMN(K13:CH13)-COLUMN(K13)+1,""),1))

Enter with Ctrl+Shift+Enter

assumes only one match

If more than one match ...

=IFERROR(INDEX($K$13:$CH$13,SMALL(IF(($K$13:$CH$13)*($M$13:$CJ$13)=$CR$13,COLUMN($K$13:$CH$13)-COLUMN($K$13)+1,""),COLUMNS($A:A))),"")

Enter with Ctrl+Shift+Enter


Drag across into CS13 etc

What will happen with the 1st equation if there is more than one match? If it returns either one, I'm fine with that one...

Hmm, there are some #DIV/0! in the range which is messing up the 1st equation. Is there a block against those?

Try

=IFERROR(INDEX(K13:CH13,SMALL(IF((K13:CH13)*(M13:CJ13)=CR13,COLUMN(K13:CH13)-COLUMN(K13)+1,""),1)),"")

... better if you removed the #DIV/0! errors by putting IFERROR(.....formula,"") or IFERROR(.....formula,0) around your formulae.

Oh sorry, what I meant was, there are some #DIV/0! in the range but also some normal values that matter. The equation cannot handle the mixture. I tried to suppress the individual #DIV/0!'s with the IFERROR to make "" in each of those cells. Then your equations returns #VALUE. I must need to replace them with something other than ""?

Ah, instead of "" if I return a 0 on error, your equation works

Hi John.

I get a few cases returning a 1 when I use:

=INDEX(K13:CH13,SMALL(IF((K13:CH13)*(M13:CJ13)=CR13,COLUMN(K13:CH13)-COLUMN(K13)+1,""),1))

Under what conditions would a 1 be returned?

Hi John.

I get a few cases returning a 1 when I use:

=INDEX(K13:CH13,SMALL(IF((K13:CH13)*(M13:CJ13)=CR13,COLUMN(K13:CH13)-COLUMN(K13)+1,""),1))

Under what conditions would a 1 be returned?

Hi John.

I get a few cases returning a 1 when I use:

=INDEX(K13:CH13,SMALL(IF((K13:CH13)*(M13:CJ13)=CR13,COLUMN(K13:CH13)-COLUMN(K13)+1,""),1))

Under what conditions would a 1 be returned?

Let me change the question. How can I get (K13:CH13)*(M13:CJ13) to only return every 3rd combination?

Bit rusty on this one: so please ....

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

OK, hope this worked. You should be able to find equations.xlsx. The 1st row is the problem. The 2nd row works. Somehow I think the data in columns C, F, I, etc. is interfering with the "search". If I could ignore those columns, that would work.

Thank for having a look.

Looking at this afresh: the formula simply confirms the presence or not of the value in column T AND appears to be the maximum.

If so this will find the maximum value ...

=MAX((B2:Q2)*(D2:S2))

Entered with Ctrl+Shift+Enter

and can be compared with T2 value

=IF(MAX((B2:Q2)*(D2:S2))=T2,T2,"")

Entered with Ctrl+Shift+Enter

Actually, I'm just doing a match type search, not a MAX. I tried to take the MAX part off, but it returns the blank.

But what is the "Business" logic: could the value in T2 NOT appear in the row?

The value will always be somewhere in the row.

=INDEX($B2:$S2,MATCH($T2,$B2:$S2,0))

will return the value ... but the logic for doing this escapes me !!!!!

No, I'm sorry. The value will not be in the row. T is the multiplied value between 2 of the values that are in the row, and I want to back out which value got multiplied. Please see the updated excel attachment.

I resorted to VBA!

Please Login or Register  to view this content.

=find_MATCH($B2:$S2,$T2)

To insert code

press Alt+F11

"Insert"==>"Module"

Copy/paste code

Indeed that works as I needed. Sorry for the confusion. Thank you.