Trying to reference an array within a formula to output corresponding value

**CVDom** · 02-10-2024, 04:27 PM

Hi,

Test Formula.xlsx

I am trying to make a Nutritional Value Chart that accurately displays all macro and micro nutrient data - appropriate to the demographic you input at the top.

I want the formulas within the min, recommended, and max columns to auto update with the correct information; according to the demographic information input above. That is to say, a child needs a different amount of protein and sodium, or a pregnant woman needs a different amounts of calories etc.

I can find all this information, and have the macros, vitamins, and mineral values on a separate sheet within the document. The only thing I don't know how to do is create a formula that references the array and correctly selects the appropriate value based on ***, and age.

A secondary question is, how can I make the charts on Nutrition Data sheet into tables with headings that span multiple columns?

The questions are posed within the document encase this wasn't clear.

Thank you!

**JeteMc** · 02-14-2024, 10:28 AM

Here is an example of how it might work.
1. Add columns (AT:AV) to the Nutrient Data sheet for minimum age, maximum age and gender/pregnant/lactating
2. Add a dropdown for pregnant/lactating in cell F2 on the Reccomended Nutrition Values sheet.
3. Populate the Vitamin A section of the Reccomended Nutrition Values sheet using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**CVDom** · 02-14-2024, 11:45 AM

Amazing! This does exactly what I was hoping for! Thank you.

If it isn't too much trouble could you explain how it works? I've tried to understand indexes before - to no avail, and this is my first time with aggregate function, or row function.

**rorya** · 02-14-2024, 12:06 PM

FYI, as you have 365, you can use:

=FILTER('Nutrient Data'!$B$19:$D$45,('Nutrient Data'!$AT$19:$AT$45<=Demographic[Age])*('Nutrient Data'!$AU$19:$AU$45>=Demographic[Age])*(('Nutrient Data'!$AV$19:$AV$45=Demographic[***])+('Nutrient Data'!$AV$19:$AV$45=$F$2)))

instead. Just enter that into B40 and clear B41 and C40:D41

**CVDom** · 02-14-2024, 12:22 PM

Heyyy, I like the spill feature, very nice! But how do I change the formula so that it doesn't spill into a row below when Pregnant or Lactating are selected, I want those values to override the original female values. - And of course for the proper age.

Thank you!

**rorya** · 02-14-2024, 12:27 PM

Can you clarify exactly what you want to happen for each possible value?

**JeteMc** · 02-14-2024, 12:28 PM

Re post #3.
I will be glad to explain as best I can; however, I would ask that you first select cell C41 and utilize the Evaluate Formula feature to see the formula in action.

**CVDom** · 02-14-2024, 12:54 PM

@Rorya I'm trying to have it filter the corresponding values from the Nutrition Data sheet to the Nutrition Values form on the "Reccomended Nutrition Values" sheet. For example Vitamin A Female 18 years old Lactating would then display only these three values: 885, 1200, 2800 (Min, Recommended, Max) in cells: B40, C40, D40. The same effect would be true for each other micro nutrient: displaying their corresponding values.

What you provided works very well, but has two sets of values when one selects Pregnant/Lactating. One set of values is the values for *** and age, the other displayed below is the corresponding value for ***, age and pregnant/lactating status. - This extra row of information is not displayed when "Neither" is selected. What should happen is that if Lactating is selected for example then the only set of values displayed should reflect the values of the age of the lactating person.

**rorya** · 02-14-2024, 12:58 PM

Oh OK - then try:

Formula:

Please Login or Register  to view this content.

I assume you can work out what to change the *** to?

**CVDom** · 02-14-2024, 12:58 PM

Thanks JeteMc! Looks like it selects a range then compares it to the selection on the Reccommended Nutrition Values sheet, True, False etc. That's as much as I understand for now. I'll have to research the Index, aggregate, and row functions on my own. Thank you!

**JeteMc** · 02-14-2024, 01:23 PM

You are correct, the INDEX function tells the formula what the range is.
The AGGREGATE function in this case could be thought of as being LARGE since the first argument is 14.
The second argument is 6 which ignores the #DIV/0! errors that will occur when a value in the array is divided by the Boolean value false.
The reason I used AGGREGATE is because the function will automatically handle arrays.
The ROW function is used to produce an array whereas the ROWS function returns a single value.
I hope that makes sense and is helpful.
Let us know if you have any questions.

**CVDom** · 02-14-2024, 01:38 PM

@rorya this works great! I just noticed though it doesn't work for people younger than 9, even though that data is available on the Nutrition Data sheet, and at that age the values ignore ***/gender and also include months. Can that be incorporated somehow? I don't mind changing the way the values are displayed (0-6 mo could be 0-0.5 years).

@JeteMC Thank you for the explanation, I'm not sure I follow the index portion or the "arguments" section of the aggregate function but I definitely have a better grasp of the concept. I'll look into these gaps in knowledge later. Thank you very much!

**JeteMc** · 02-14-2024, 02:01 PM

Here is a link to the INDEX function: https://support.microsoft.com/en-us/...2-b56b061328bd
The syntax is INDEX(array, row_num, [column_num])
array is the range to cells that will be evaluated using the row_num and column_num arguments.
In the case of my formula the AGGREGATE function supplies the row_num and the COLUMNS function supplies the column_num.
Here is a link to the AGGREGATE function: https://support.microsoft.com/en-us/...6-e19993fa26df
The syntax is AGGREGATE(function_num, options, array, [k])
function_num is the first argument and options is the second.

**CVDom** · 02-16-2024, 09:36 AM

For those who come to this thread in the future. Here is the final result

Please Login or Register  to view this content.

The INDEX allows the SEQUENCE function to control how many levels of spill result (in this case 3). The FILTER; filters... Then the array is set: "B2_Riboflavin" (I made each element, vitamin, and trace mineral into it's own table. I also made the age range, and *** ($EX) into it's own table at the beginning of each Nutrition Data sheet chart. Couple more changes: the infant and children ranges were duplicated and added to the beginning of each gender so as to keep things simple.

Please Login or Register  to view this content.

The formula for zinc is slightly different, multiplying the result by 1.5 if the demographic is a vegetarian (because the bio-availability of zinc is lower in plants, than in animals).

Or you can add stuff as is the case with Vitamin C:

Please Login or Register  to view this content.

Because smoking increases oxidative stress and metabolic turnover of vitamin C, the requirement for smokers is increased by 35 mg/day.

Thanks all for your help! SOLVED

**JeteMc** · 02-16-2024, 11:05 AM

Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Trying to reference an array within a formula to output corresponding value

LinkBack

Thread Tools

Rate This Thread

Display

Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Re: Trying to reference an array within a formula to output corresponding value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Counting entries from an array formula output

[SOLVED] Array to embed formula and if number of output is met output Blank

[SOLVED] Cell reference, output dates to numeric. Should output as text

Cell reference must work with formula output, not formula itself

convert array formula output to string

Function/ formula to output a cell reference

customise array formula output

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions