# Formula for chart



## yitzymerm (Dec 13, 2022)

Please see snapshot of ht & wt chart below, Column C is showing the height, Row 7 are the ages & Row 6 is the category result, and the weight amounts in the chart are the maximum allowed (i.e. Age 25, Height 4.8 can weigh up to 130 to fall into excellent category, at 131 lbs they are already in the 'good' category until 152 etc.)

I would like to be able to enter the Ht, Wt, & age in column B & the result should automatically come up in B4 (excellent, good or poor), please advise simplest & best formula that should be used.

Thanks in advance.

View attachment 80818


----------



## yitzymerm (Dec 13, 2022)

Seems like the chart didn't upload well in original post, please see below.


----------



## HongRu (Dec 13, 2022)

suggest you to use XL2BB Mini Sheet.
Your chart cells formats will affect how the formula work.
It's hard only by a picture.


----------



## HongRu (Dec 13, 2022)

Try.
The formula in B4 is
=IF($B$2>VLOOKUP($B$1,$C$8:$J$28,7),"Poor",IF($B$2>HLOOKUP(B3,D7:H28,MATCH($B$1,$C$8:$C$28,0)+1,1),"Good","Excellent"))

The cells below "Excellent" should be replace by 20, 24, 30, 50, 60.
HTH.
身高體重.xlsmABCDEFGHIJ1H4'8"2W1303Age244RESULTExcellent56EXCELLENTGoodPoor7H/A2024305060All agesAll ages84'8"12513013213613815219194'9"128132135139141155195104'10"114'11"125'0"135'1"145'2"155'3"165'4"175'5"185'6"195'7"205'8"215'9"225'10"235'11"246'0"256'1"266'2"276'3"286'4"工作表8 (2)Cell FormulasRangeFormulaB4B4=IF($B$2>VLOOKUP($B$1,$C$8:$J$28,7),"Poor",IF($B$2>HLOOKUP(B3,D7:H28,MATCH($B$1,$C$8:$C$28,0)+1,1),"Good","Excellent"))


----------



## HongRu (Dec 13, 2022)

Here is a version that you can keep your titles "20-23", "24-29"...etc.
The formula in B4 is
=IF($B$2>VLOOKUP($B$1,$C$8:$J$28,7),"Poor",IF($B$2>INDEX($D$8:$H$28,MATCH($B$1,$C$8:$C$28,0),MATCH(B3,VALUE(LEFT($D$7:$H$7,2)),1)),"Good","Excellent"))

And you should press CTRL+SHIFT+ENTER to enter array formulas.
Good Luck.
身高體重.xlsmABCDEFGHIJ1H4'9"2W1343Age304RESULTExcellent56EXCELLENTGoodPoor7H/A20-2324-2930-4950-5960+All agesAll ages84'8"12513013213613815219194'9"128132135139141155195104'10"114'11"125'0"135'1"145'2"155'3"165'4"175'5"185'6"195'7"205'8"215'9"225'10"235'11"246'0"256'1"266'2"276'3"286'4"工作表8 (3)Cell FormulasRangeFormulaB4B4=IF($B$2>VLOOKUP($B$1,$C$8:$J$28,7),"Poor",IF($B$2>INDEX($D$8:$H$28,MATCH($B$1,$C$8:$C$28,0),MATCH(B3,VALUE(LEFT($D$7:$H$7,2)),1)),"Good","Excellent"))Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## yitzymerm (Dec 14, 2022)

HongRu said:


> suggest you to use XL2BB Mini Sheet.
> Your chart cells formats will affect how the formula work.
> It's hard only by a picture.


Hi HongRu,
Thanks so much for your help!!!
I have another chart which is a bit more complex than the previous one, would really appreciate if you can help me with this as well, on this one on highest rating it depends between male & female and on age, the other classes does not differ between gender and age, this chart has also much more levels, (want the rating result in cell B5 based on B1:B4 info).

I'll attach here the Mini Sheet so should be easier for you.

Buildchart.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1Enter M/FAges20-2425-2930-3940-4950-5960+20-2425-2930-3940-4950-5960+All Ages2Enter HeightHt/RatingMale - EliteFemale  - ElitePreferredStandardClass 2Class 3Class 4Class 6Class 8Class 10Class 123Enter Weight4.8128131132134136138124126130133134135151191202213222231240N/AN/A4Enter Age4.9131133135137139141126130133136138138154195206217226235244N/AN/A5Rating Result4.10135137139141143145131134137139141142158199210221230239249N/AN/A64.11138140142144146148134137139142144145161203214224233243253N/AN/A7514114314514714915113613914214514714816520721822923924825726627585.114514714915115315514014314614915115216921222323324325226127027995.2147149152154156158143146149152154155172216227237247256265274283105.3151153155158160162146149153156158159177221232243253263273282291115.4156158160162165167151154157161162164183227238249260270280290299125.5159162164166169171154158161164167168188233244255266277287297306135.6164166169171174176159162166169171173192239250261272283294304313145.7168170173175178181163166170173175177197245256268279290302312321155.8171174176179182185166170173177179181202252264276287298310320329165.9176179182185187190171174178183185186207259272284296307319329338175.10182184187190192195175179184187190191212266279291303315326337347185.11185188191194196199179184187191194195217273286299311323334345355196191194197199202205185189193197199201223281294307319331342353363206.1196199201204207210190194198202204206228289302315327339350362372216.2199202205208211214193197202206208210235297310323335347358371381226.3206209212215218221200204208212215217241305318331343355367380390236.4212215218221224227205209214218221223248313326339351363376389399246.5217220223227230234210215219224227228255321334347359372385398408256.6224227230234238241217221226232235236263328342355367381394407417266.7230235238241245248223228234238241243270336350363375390403416426276.8238241245248252255229235240245248250286.9243247250254257261236241246251254256296.10249252256260263267241246251257260262Sheet2 (3)


----------



## HongRu (Dec 14, 2022)

Try.

B5
=IF(B3<=INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),"Elite",INDEX($Q$2:$Y$2,1,IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1)+1,1)))

If not MS365, press CTRL+SHIFT+ENTER to enter array formulas.

The formula is long enough.
So I don't write more to prevent some errors from happening, 
ex: 
1.When weight more than the most class, it shows "#REF!".
2. Since Class10, Class12's "N/A" between height 4.8 to 4.11. (X3:Y6),  the weight more than Class 8 shows "Class 10".
3. While the blank data in Height 6.9 to 6.10 and weight beyond elite (Q27:Y29) , it shows "Preferred".

PS. the cells "D5", "D17", D29" should be "4.10", "5.10", "6.10", not "4.1", "5.1", "6.1"
身高體重性別.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1Enter M/FMAges20-2425-2930-3940-4950-5960+20-2425-2930-3940-4950-5960+All Ages2Enter Height5.10Ht/RatingMale - EliteFemale  - ElitePreferredStandardClass 2Class 3Class 4Class 6Class 8Class 10Class 123Enter Weight2514.8128131132134136138124126130133134135151191202213222231240N/AN/A4Enter Age254.9131133135137139141126130133136138138154195206217226235244N/AN/A5Rating ResultStandard4.10135137139141143145131134137139141142158199210221230239249N/AN/A64.11138140142144146148134137139142144145161203214224233243253N/AN/A7514114314514714915113613914214514714816520721822923924825726627585.114514714915115315514014314614915115216921222323324325226127027995.2147149152154156158143146149152154155172216227237247256265274283105.3151153155158160162146149153156158159177221232243253263273282291115.4156158160162165167151154157161162164183227238249260270280290299125.5159162164166169171154158161164167168188233244255266277287297306135.6164166169171174176159162166169171173192239250261272283294304313145.7168170173175178181163166170173175177197245256268279290302312321155.8171174176179182185166170173177179181202252264276287298310320329165.9176179182185187190171174178183185186207259272284296307319329338175.10182184187190192195175179184187190191212266279291303315326337347185.11185188191194196199179184187191194195217273286299311323334345355196191194197199202205185189193197199201223281294307319331342353363206.1196199201204207210190194198202204206228289302315327339350362372216.2199202205208211214193197202206208210235297310323335347358371381226.3206209212215218221200204208212215217241305318331343355367380390236.4212215218221224227205209214218221223248313326339351363376389399246.5217220223227230234210215219224227228255321334347359372385398408256.6224227230234238241217221226232235236263328342355367381394407417266.7230235238241245248223228234238241243270336350363375390403416426276.8238241245248252255229235240245248250286.9243247250254257261236241246251254256296.10249252256260263267241246251257260262工作表1 (4)Cell FormulasRangeFormulaB5B5=IF(B3<=INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),"Elite",INDEX($Q$2:$Y$2,1,IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1)+1,1)))Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## yitzymerm (Dec 15, 2022)

HongRu said:


> Try.
> 
> B5
> =IF(B3<=INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),"Elite",INDEX($Q$2:$Y$2,1,IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1)+1,1)))
> ...


Thanks so much HongRu, you're the man!!!

Took me a while to fully understand the complex formula you wrote but works like a charm.

Now if i want to add let's say in cell B6 based on the result how much over the limit he was from the previous better rating, what formula would I put?

In example, Male age 20, Ht 5.5, Wt 165 which comes out "preferred" I want that cell to tell me how many LBS he was over, which in this case it would be 8 Lbs, as the limit for Elite is 157.

Thanks loads for all your help.


----------



## HongRu (Dec 15, 2022)

yitzymerm said:


> In example, Male age 20, Ht 5.5, Wt 165 which comes out "preferred" I want that cell to tell me how many LBS he was over, which in this case it would be 8 Lbs, as the limit for Elite is 157.


157 and over 8? 
Not 159  over 6?


----------



## HongRu (Dec 15, 2022)

Try.

The formulas are too complex to debug.
So I am not sure the formula are all right.
And I make some conditional formats to check values easily.

Book.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1Enter M/FMAges20-2425-2930-3940-4950-5960+20-2425-2930-3940-4950-5960+All Ages2Enter Height5.1Ht/RatingMale - EliteFemale  - ElitePreferredStandardClass 2Class 3Class 4Class 6Class 8Class 10Class 123Enter Weight2234.8128131132134136138124126130133134135151191202213222231240N/AN/A4Enter Age604.9131133135137139141126130133136138138154195206217226235244N/AN/A5Rating ResultClass 24.10135137139141143145131134137139141142158199210221230239249N/AN/A6Better Rate114.11138140142144146148134137139142144145161203214224233243253N/AN/A7514114314514714915113613914214514714816520721822923924825726627585.114514714915115315514014314614915115216921222323324325226127027995.2147149152154156158143146149152154155172216227237247256265274283105.3151153155158160162146149153156158159177221232243253263273282291115.4156158160162165167151154157161162164183227238249260270280290299125.5159162164166169171154158161164167168188233244255266277287297306135.6164166169171174176159162166169171173192239250261272283294304313145.7168170173175178181163166170173175177197245256268279290302312321155.8171174176179182185166170173177179181202252264276287298310320329165.9176179182185187190171174178183185186207259272284296307319329338175.10182184187190192195175179184187190191212266279291303315326337347185.11185188191194196199179184187191194195217273286299311323334345355196191194197199202205185189193197199201223281294307319331342353363206.1196199201204207210190194198202204206228289302315327339350362372216.2199202205208211214193197202206208210235297310323335347358371381226.3206209212215218221200204208212215217241305318331343355367380390236.4212215218221224227205209214218221223248313326339351363376389399246.5217220223227230234210215219224227228255321334347359372385398408256.6224227230234238241217221226232235236263328342355367381394407417266.7230235238241245248223228234238241243270336350363375390403416426276.8238241245248252255229235240245248250286.9243247250254257261236241246251254256296.10249252256260263267241246251257260262Sheet(2)Cell FormulasRangeFormulaB5B5=IF(B3<=INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),"Elite",INDEX($Q$2:$Y$2,1,IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1)+1,1)))B6B6=IF($B$3<=INDEX($Q$3:$Q$29,MATCH($B$2,$D$3:$D$29,0)),B3-INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),IF(INDEX($Q$3:$Y$29,MATCH($B$2,$D$3:$D$29,0),IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1),1))="",$B$3-INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),$B$3-INDEX($Q$3:$Y$29,MATCH($B$2,$D$3:$D$29,0),IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1),1))))Press CTRL+SHIFT+ENTER to enter array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueQ2:Y2Expression=$B$5=Q$2textNOQ2:Y2Expression=$D2=$B$2textNOQ3:Y29Expression=$B$5=Q$2textNOP1,J1,J3:J29,P3:P29Expression=$B$4>=VALUE(LEFT(J$1,2))textNOE1:P1,E3:P29,E2,K2Expression=IFERROR(AND($B$4>=VALUE(LEFT(E$1,2)),$B$4<=VALUE(RIGHT(E$1,2))),FALSE)textNOD3:Y29Expression=$D3=$B$2textNO


----------



## yitzymerm (Dec 13, 2022)

Please see snapshot of ht & wt chart below, Column C is showing the height, Row 7 are the ages & Row 6 is the category result, and the weight amounts in the chart are the maximum allowed (i.e. Age 25, Height 4.8 can weigh up to 130 to fall into excellent category, at 131 lbs they are already in the 'good' category until 152 etc.)

I would like to be able to enter the Ht, Wt, & age in column B & the result should automatically come up in B4 (excellent, good or poor), please advise simplest & best formula that should be used.

Thanks in advance.

View attachment 80818


----------



## yitzymerm (Dec 22, 2022)

HongRu said:


> Try.
> 
> The formulas are too complex to debug.
> So I am not sure the formula are all right.
> ...


Hi HongRu,

Thank you very much for helping me out with this.

I was able to implement it on this chart and on some other similar charts that I had, will mark this done, was truly a great help!!!!!


----------

