# This bugger doesn't work

#### apurk45

##### Board Regular
Hi
Can any of you tell what is wrong with my formula?

=IF(AND(K8>0,M8="IJK",MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F8)),G8,IF(MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F9),G9,IF(MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F10),G10,"")))

My cell K8=0 and for ungodly reason my formula does not recognize it.
Also if you have any suggestion how to make this formula shorter would be appreciated.

Thanks
Apurk45 ### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Hello,

Does K8>=0 work?

##### MrExcel MVP
Try...

=IF(N(K8)*(M8="IJK")*ISNUMBER(MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),INDEX(G8:G10,MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),"")

which must be confirmed with control+shift+enter instead of just with enter.

In which cell do you put this formula?

#### apurk45

##### Board Regular
No does not work.

When K8=0 I should get "" in formula cell otherwise value from G8, G9 or G10.

Currenty I am getting the value from G cells even though my K8=0.

Apurk45

#### apurk45

##### Board Regular

Did not work - same problem. I used Crl+Shf+Enter.

I putting my formula into H9.

Apurk45

#### Ahnold

##### Well-known Member
Try this:
=IF(AND(K8>0,M8="IJK",MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F8)),G8,IF(AND(K8>0,MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F9)),G9,IF(AND(K8>0,MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F10)),G10,"")))

##### MrExcel MVP
apurk45 said:

Did not work - same problem. I used Crl+Shf+Enter.

I putting my formula into H9.

Apurk45

Did not work means what? An error value?

If you like, post the data in the area F8:G10. And explain in words what must be computed?

#### apurk45

##### Board Regular
My apologies your formula works great. I fat fingered some incorrect cell #.

Ahnold

Thank you very much Guys.

PS.
Even though your formula works perfect – I am lost as to understanding how it works. Would you mind explaining logic of your combabulation?

Thanks
Apurk45

##### MrExcel MVP
apurk45 said:
My apologies your formula works great. I fat fingered some incorrect cell #.

...
Even though your formula works perfect – I am lost as to understanding how it works. Would you mind explaining logic of your combabulation?...

We can break this up in 2 cells to gain more efficiency...

Instead of the following in H9...

=IF(N(K8)*(M8="IJK")*ISNUMBER(MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),INDEX(G8:G10,MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),"")

in I9 control+shift+enter:

=MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)

then in H9 just enter:

=IF(N(K8)*(M8="IJK")*ISNUMBER(I9),INDEX(G8:G10,I9),"")

Now explaining my "confabulation" becomes much easier:

The formula in I9, if successful, establishes the position of the (first instance of the) MAX value in F8:F10. The presence of ABS applied to a multicell range requires a formula that operates on arrays...

The IF formula in H9 has a condition (actually: subconditions strung together with *, which behaves like boolean AND)...

N(K8) gives 0 if K8 houses either a real 0 or text like "", otherwise returns the non-zero number that K8 houses.

The M8="IJK" bit either returns TRUE or FALSE.

The ISNUMBER(I9) returns TRUE if the MATCH formula in I9 returns a position indicating (non-zero) number, otherwise FALSE when the MATCH formula returns #N/A.

So (a) 0 or Non-Zero multiplied with (b) TRUE or FALSE multipled with (c) TRUE or FALSE is bound to return either a 0 or a non-zero number. Excel interprets a 0 as FALSE and a non-zero number as TRUE. A non-zero number as result of IF's condition leads to the evaluation of the

INDEX(G8:G10,I9)

which fetches the value from G8:G10 at pos I9. A 0 as result of IF's condition, leads to "".

#### apurk45

##### Board Regular
Re: This bugger doesn't work [b](Solved)[/b]

Thank you very much for your time. You are very good and VERY helpful.

Apurk45

Replies
5
Views
437
Replies
9
Views
210
Replies
6
Views
534
Replies
1
Views
220
Replies
15
Views
566

### Forum statistics

1,186,366
Messages
5,957,448
Members
438,306
Latest member
Crystal_Blue ### 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