Find value in array

[Epinn]

The data is in B2:F4, so Biff used that throughout.

Please remember that I substitute B2:F4 into Biff's original formula to suit Brook6's table. Hope there is no copyright. I have included Biff's original formula in my other post for your reference. I am sure Bob's analysis still stands.

Epinn

Because he is addressing the data presented, rather than throwing up some
seemingly spurious hard-coded value. The data is in B2:F4, so Biff used that
throughout. If the OP then wanted to extend it for a bigger data range, it
would be obvious what needs changing.

Good coding practice IMO.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Epinn]

<< So, I am wondering if there is some kind of built-in error checking in Biff's formula. Which <<function does the job automatically?

I think it is MATCH ( ).

Epinn

All this ^0 business is beyond me. I might even have a problem deciphering INDEX/MAX/ROW formula. However, I can read and compare.

Under the other thread, the poster had the same scenario as Brook6 had.

While Biff suggested INDEX/MATCH, interestingly enough another expert suggested INDEX/MIN/ROW.
**********************************************************************************
Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(D26,MATCH(TRUE,MMULT(--(A2:C6=G2),TRANSPOSE(COLUMN(A2:C6)^0))>0,0))

=IF(SUM(--(A1:C5=B8)),INDEX(D15,MIN(IF(A1:C5=B8,ROW(A1:A5),""))),"N/A")
also array entered

**********************************************************************************
How does Biff think of his formula compared to INDEX/MIN/ROW?

In Biff's words: "I sure went with overkill on that one!"

Bob: "Good coding practice IMO."

Biff's formula works perfectly as it takes care of the entries that are not found in the table. I think it gives an NA error.

However, for the other formula, be it under this thread or the other thread, I had to draw the creators' attention to the fact that the formulae did not take care of errors.

So, I am wondering if there is some kind of built-in error checking in Biff's formula. Which function does the job automatically?

I probably won't understand, but as always I feel like chatting.

Thank you for reading.

Epinn

Because he is addressing the data presented, rather than throwing up some
seemingly spurious hard-coded value. The data is in B2:F4, so Biff used that
throughout. If the OP then wanted to extend it for a bigger data range, it
would be obvious what needs changing.

Good coding practice IMO.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Roger Govier]

Hi

Raising any number to the power of 0 results in a value of 1, so the
result of COLUMN(B:F) would be 2,3,4,5,6 which when raised to the power
of zero results in 1,1,1,1,1

The easiest way to consider it is by starting with a number like 2^4
which gives 16, and 2^3 which gives 8 and 2^2 which gives 4, So as we
come down by each power, we are dividing by the original number so
extending onward, 2^1 gives 2 and 2^0 gives 1. The only exceptions to
this of course are 0 and 1 themselves which when raised to any power
will remain as 0 and 1.

If you continue the series, 2^-1 would give 0.5 and 2^-2 would give 0.25
in other words, each is the reciprocal of the positive power.

--
Regards

Roger Govier


All this ^0 business is beyond me. I might even have a problem
deciphering INDEX/MAX/ROW formula. However, I can read and compare.

Under the other thread, the poster had the same scenario as Brook6 had.

While Biff suggested INDEX/MATCH, interestingly enough another expert
suggested INDEX/MIN/ROW.
**********************************************************************************
Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(D26,MATCH(TRUE,MMULT(--(A2:C6=G2),TRANSPOSE(COLUMN(A2:C6)^0))>0,0))

=IF(SUM(--(A1:C5=B8)),INDEX(D15,MIN(IF(A1:C5=B8,ROW(A1:A5),""))),"N/A")
also array entered

**********************************************************************************
How does Biff think of his formula compared to INDEX/MIN/ROW?

In Biff's words: "I sure went with overkill on that one!"

Bob: "Good coding practice IMO."

Biff's formula works perfectly as it takes care of the entries that are
not found in the table. I think it gives an NA error.

However, for the other formula, be it under this thread or the other
thread, I had to draw the creators' attention to the fact that the
formulae did not take care of errors.

So, I am wondering if there is some kind of built-in error checking in
Biff's formula. Which function does the job automatically?

I probably won't understand, but as always I feel like chatting.

Thank you for reading.

Epinn

Because he is addressing the data presented, rather than throwing up
some
seemingly spurious hard-coded value. The data is in B2:F4, so Biff used
that
throughout. If the OP then wanted to extend it for a bigger data range,
it
would be obvious what needs changing.

Good coding practice IMO.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

I think we took that as read Epinn; I am sure that there is no copyright; I
am sure that my analysis still holds good, after all as you yourself said,
you adapted it without understanding it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)


Please remember that I substitute B2:F4 into Biff's original formula to suit
Brook6's table. Hope there is no copyright. I have included Biff's
original formula in my other post for your reference. I am sure Bob's
analysis still stands.

Epinn

Because he is addressing the data presented, rather than throwing up some
seemingly spurious hard-coded value. The data is in B2:F4, so Biff used that
throughout. If the OP then wanted to extend it for a bigger data range, it
would be obvious what needs changing.

Good coding practice IMO.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Biff was referring to the fact that there was a simpler formula (which you
should know, you joined in that thread). Looking at it, and adjusting to our
OPs data and his starting row, we can use

=INDEX(A2:A4,MIN(IF(B2:F4=A7,ROW(A2:A4)-MIN(ROW(A2:A4))+1,"")))

as an array formula.

Time to put this one to bed?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

All this ^0 business is beyond me. I might even have a problem deciphering
INDEX/MAX/ROW formula. However, I can read and compare.

Under the other thread, the poster had the same scenario as Brook6 had.

While Biff suggested INDEX/MATCH, interestingly enough another expert
suggested INDEX/MIN/ROW.
****************************************************************************
******
Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(D26,MATCH(TRUE,MMULT(--(A2:C6=G2),TRANSPOSE(COLUMN(A2:C6)^0))>0,0))

=IF(SUM(--(A1:C5=B8)),INDEX(D15,MIN(IF(A1:C5=B8,ROW(A1:A5),""))),"N/A")
also array entered

****************************************************************************
******
How does Biff think of his formula compared to INDEX/MIN/ROW?

In Biff's words: "I sure went with overkill on that one!"

Bob: "Good coding practice IMO."

Biff's formula works perfectly as it takes care of the entries that are not
found in the table. I think it gives an NA error.

However, for the other formula, be it under this thread or the other thread,
I had to draw the creators' attention to the fact that the formulae did not
take care of errors.

So, I am wondering if there is some kind of built-in error checking in
Biff's formula. Which function does the job automatically?

I probably won't understand, but as always I feel like chatting.

Thank you for reading.

Epinn

Because he is addressing the data presented, rather than throwing up some
seemingly spurious hard-coded value. The data is in B2:F4, so Biff used that
throughout. If the OP then wanted to extend it for a bigger data range, it
would be obvious what needs changing.

Good coding practice IMO.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Kevin Vaughn]

I know this thread is long closed, but, I THOUGHT that 0^0 would still be 1
(because I thought every value raised to the 0th power was 1,) but when I
tried it in Excel, I got #num (FWIW.)

 

[Harlan Grove]

I know this thread is long closed, but, I THOUGHT that 0^0 would still be 1
(because I thought every value raised to the 0th power was 1,) but when I
tried it in Excel, I got #num (FWIW.)

....

#NUM! is the appropriate result, just as it's the same result as given
by LOG(0). 0^0 is undefined for the very simple reason that if 0^0 =
1, then 0^0 = 0^(n - n) for any n, and so 0^(n - n) = 0^n / 0^n = 0 /
0. But *any* number divided by zero is undefined, *including* zero
itself. All sorts of inconsistencies arise if 0/0 = 0^0 = 1, which is
why it doesn't.