2

Okay, so I have an issue I'm trying to solve all in one formula.

I have a setup of tables:

my table

How many NFPI (Number of Fruit x Percent Intensity) do I have for Apples?

I need to scan column G of Table 3 against Column B of Table 1 to see which rows have a kind of Apple in them.

Then I need to find the total number of fruits for each occurrence of Apple:

(Number of trees * Tree fruit) + ground fruit

And find that total number of fruit in Table 2 at the proper Percent Intensity and add up all occurrences

So it would look like:

(1 * 3) + 2 = 5 Red Apples. NFPI of 5 fruits at 97% intensity = 1.67  
(2 * 2) + 0 = 4 Green Apples. NFPI of 4 fruits at 98% intensity = 2  
(1 * 2) + 1 = 3 Yellow Apples. NFPI of 3 fruits at 97% intensity = 1  
**total NFPI = (1.67 + 2 + 1) = 4.67**

I'm trying to do this all in one formula. The formula I've tried to use incorporates a vlookup into an array formula, but it keeps feeding me the wrong answer. Here's the formula I've tried:

{=SUM(IF(ISERROR(MATCH(G12:G16,B3:B5,0))=FALSE,VLOOKUP(H12:H16,F4:K8,(I12:I16*J12:J16)+K12:K16+1),0))}

I can't figure out why it doesn't work or a way to make it work. I thought maybe a SUMPRODUCT formula could help, but I couldn't figure that out either. I know I could just find the NFPI of each apple entry and enter it in another column next to the Ground Fruit column and then just simply put a SUM formula at the bottom of that to add it all up, but I'm trying to find the sum without doing that if possible.

Any help would be appreciated!

  • Sean, I finally got this to work the way you wanted it to. See my edited first answer below. – Bandersnatch Mar 7 '18 at 15:07
1

EDIT: A recent answer from @ScottCraner used the "de-referenced" INDEX() formula and his answer made me decide to take another crack at this problem. The same approach I had (unsuccessfully) tried before worked perfectly the second time around. I'll describe the solution below.

Background about the De-referenced INDEX() formula:

Sean, you've made an admirable attempt to use array formulas to do what you need to do. The problems you're having are related to the way Excel handles arrays. Some formulas can use arrays as arguments and some can't.

I did some digging into this and I learned some very bizarre, arcane things about using arrays in Excel's INDEX() formula that I didn't know before. To understand how this formula works, let's start at the end.

The very last thing that your formula would do is sum three (discontinuous) values from the 2D array that is your Table 2.

INDEX(array,row_num,col_num) can return a single value from a 2D array, and it can also return a whole column or row. It seems like it ought to be able to return a list of values. So let's test it.

This formula would (in a perfect world) return the sum you're looking for from Table 2:

=SUM(INDEX(G4:K8,{3,2,3},{5,4,3}))

That should add the elements from row 3,column 5 plus row 2, column 4 plus row 3, column 3. But it doesn't, it just returns 1.67 which is the first element referenced.

Searching online produces references (including one here on StackOverflow) that say INDEX() will return an array, but only if you de-reference the formula (that's the "bizarre" part). The "arcane" part is how to do that. This is the "de-referenced" formula:

=SUM(INDEX(G4:K8,N(IF(1,{3,2,3})),N(IF(1,{5,4,3}))))

This formula gives the correct answer: 4.67.

In the formula, the IF() treats the 1 as True, so it returns the array of numbers, and the N() returns the array of numbers if they are numbers, which they are. Why the IF() and N() are required to make the formula work correctly is anybody's guess. In Scott's formula, he had to also multiply his array (it was a range reference) by 1.

But, now we have a formula that gives the right answer. And hopefully, all we have to do is replace the array constants with calculated arrays using your other data.

New information starts here.

For the row_num's in the formula above {3,2,3}, we need the positions of the percent intensities in F4:F8 associated with the chosen fruit varieties. First, we'll get an array of the positions of the Apples in G12:G16 of your Table 3:

=MATCH(B3:B5,G12:G16,0)

This is an array formula and must be entered with CTRLShiftEnter, rather than just Enter.

This formula looks for the list of Apple varieties from Table 1 in Column G of Table 3 and returns an array of their positions.

If you select the formula in the formula bar and hit F9, you'll see the value of the formula is the array {1,3,4}, the positions of the Apples in Column G of Table 3.

Now we need the PI's associated with those positions. This INDEX() formula looks in Column H and uses the above array as the row_num's. Here, the row_num's have to be "de-referenced":

=INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

This formula returns the array {0.97,0.98,0.97}, the PI's of the Apples. So far, so good. Next we use that array as the lookup values in a MATCH() formula that looks in F4:F8, the PI index of your Table 2:

=MATCH(INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0)))),F4:F8,0)

This formula returns the array {3,2,3}, and those are the row_num's needed for the final formula.

Next we need the col_num's {5,4,3}, which are the total number of fruits for each of the Apple varieties. We'll get this from Table 3, but first we need to calculate the total number of fruits for all of the fruit varieties. This (calculated) array is a list of those totals:

(I12:I16*J12:J16)+K12:K16

To get the total number of fruits for the Apple varieties, we'll use that array in an INDEX(), with the same (de-referenced) row_num's as before:

=INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

This formula returns the array {5,4,3}, and those are the col_num's needed for the final formula.

Putting this all together, the list of NFPI's is:

=INDEX(G4:K8,MATCH(INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0)))),F4:F8,0),INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

This formula returns the array {1.67;2;1}. Those are the NFPI's for Apples, and now we just have to add them up.

But not quite yet, there's a minor issue to take care of first. All three of the Apple varieties can be found in Table 3, but this is not true for Oranges. The formulas above return arrays with #N/A in them where the Small Orange variety can't be found. This doesn't cause any problems until it comes time to add up the values.

So before taking the sum, we convert the #N/A's to 0 with an IFERROR() formula. Here is the final formula:

=SUM(IFERROR(INDEX(G4:K8,MATCH(INDEX(H12:H16,N(IF(1,MATCH(C3:C5,G12:G16,0)))),F4:F8,0),INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(C3:C5,G12:G16,0))))),0))

This formula returns 4.67 for the Apples and 5.75 for the Oranges.

Sean, I hope this can still be useful. Sorry for the long delay.

  • Wow, thank you very much. This is really interesting. I don't entirely understand how the "de-referencing" and "arcane" stuff works in that formula, but it does indeed work like you said. I really appreciate the help so far. Hope to hear back again from you! – Sean Feb 21 '18 at 2:11
  • You're welcome. The formula is pretty strange. The IF() treats the 1 as True, so it returns the array of numbers, and the N() returns the array of numbers if they are numbers, which they are. Why this does anything at all, (but makes the formula work correctly) is beyond me. – Bandersnatch Feb 21 '18 at 2:17
  • Thanks for the explanation. I had no idea T() and N() formulas even existed; I had to look them up. They seem like some of the original formulas from the early days of Excel. – Sean Feb 21 '18 at 2:28
  • Another question: I understand how that vlookup of yours returns the array you get, but how do you get the returned array to display in excel? All I ever is #VALUE! errors. I assume that's because a cell can only display one value. Is there a way to convert the returned array into text and then display that in the cell where the formula is? Mostly asking this for future reference when I run into another difficult issue I'm trying to figure out. – Sean Feb 21 '18 at 2:33
  • You know to enter array formulas with CTRL-Shift-Enter, right? Often, that will clear up the #VALUE error. To see the whole array that is returned, click in the formula bar and highlight the formula (or portion of the formula) that you want to see the result for. Hit F9, and Excel shows the value of what you highlighted. Be sure to hit CTRL-Z afterwards, or Excel replaces your formula with the result. :-) – Bandersnatch Feb 21 '18 at 2:38
1

EDIT See my other answer on this page. It turns out that INDEX() CAN return a list of (discontinuous) values from an array.

Here is another way to accomplish what you're trying to do, although the formula turns out to be very long.

The following formula looks up each of the three NFPI's in your Table 2 and adds them together.

=INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0))+INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B4,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B4,G$12:G$16,0)),G$3:K$3,0))+INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B5,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B5,G$12:G$16,0)),G$3:K$3,0))

How it works: Each term in the sum is an INDEX() function that returns an element from Table 2 by specifying the row_num and column_num. For the first term, the row_num is found by first, using an INDEX() that looks for B3 (Red Apple) in Table 3 and returns the associated % Intensity:

INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0))

Then this PI is used in a MATCH() to return the correct row of Table 2:

MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0)

The column_num is found by first finding the correct number of fruit. I added an extra column to your Table 3 that calculated the total number of fruit in L12:L16. If that isn't an option, you can calculate the number of fruit "on the fly" by replacing L$12:L$16 with (I$12:I$16)*(J$12:J$16)+(K$12:K$16):

INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0))

As for the row_num, this is used in a MATCH() to return the correct row of Table 2:

MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0)

Now that the row and column are known for Red Apple, the first term in the sum is:

=INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0))

The remaining two terms for Green Apple and Yellow Apple are the same formula, but with B4 and B5 replacing B3.

I hope this helps and best of luck.

  • I appreciate the help man. I tend to make extremely long formulas like this all the time, so I enjoyed figuring out how you did it. My original question and image provided above was some invented data for ease of understanding. With the actual data I'm using, the equivalent of Table 1 above has 30+ entries. So I think this solution, while it works, may be excessive if it includes the sum of 30+ INDEX functions. It appears to not be possible to use the entirety of Table 1 as a reference and the workaround is to instead use each entry (B3, B4, B5 as you did). – Sean Feb 28 '18 at 6:33
  • But I voted your answer as the solution to the question because it technically is a solution for the data in my original question. Cheers! – Sean Feb 28 '18 at 6:38
-2

On the basis of information provided by OP and the sample Formula, I've found a Solution to Extract Fruit name, Total of Fruits and NFPI.

enter image description here

Formula in Cell A12 to extract Fruit Type:

=IFERROR(VLOOKUP(A2,$D$2:$H$6,1,0),"")

Formula in D12 to generate Table Of Apples:

{=VLOOKUP(A2,$D$2:$H$6,{1,2,3,4,5},FALSE)}

NB: Finish the Formula with Enter then drag the Formula up to Column H and press F2 then Finish this Formula with Clrt+Shift+Enter then after drag it down. You get Table of Apples with other Values.

Formula in Cell B12 to find the Total Fruit Value (as OP suggested the sample Formula):

`=IF(VLOOKUP(A2,$D$2:$H$6,1,0)=$D12,((F12*G12)+H12),0)`

For NFPI the Formula in Cell C12:

=(E12*IF(VLOOKUP(A2,$D$2:$H$6,1,0)=$D12,((F12*G12)+H12),0))

NB: This is a tentative Formula I've created on the basis of the information I've found in OP. (If you show me how you found 1.67 then I just alter the equation).

I do believe this help you.

  • Hi Rajesh, I'm not sure how this works. I don't see how it uses the Percent Intensity - Number of Fruits table (Table 2 from my picture) like I want it to. – Sean Feb 20 '18 at 10:15
  • @Sean,, I've solved 2 of the issues. 1. Extract Fruit Name and 2. Total of the Fruits (as you shown, (1 * 3) + 2 = 5). Actually how you are calculating, NFPI of 5 Fruits = 1.67,, about this I'm little confused. If you show me the equation, the I'll suggest a Formula how to get 5 and then 1.67 together. But let me remind for all 3 exercise SINGLE formula is not possible!! – Rajesh S 37 mins ago – Rajesh S Feb 20 '18 at 11:20
  • @Sean,, check I've edited my answer which is also calculating NFPI. Again I would like to remind you, about NFPI still I'm confuse that how to get 1.67,, if you show me the equation then I could alter my Formula. – Rajesh S Feb 20 '18 at 12:01
  • Thanks for the edits, Rajesh. The 1.67 is a value from Table 2 in my picture. – Sean Feb 20 '18 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.