Find the highest result in a range of formulas

Nigeljones

New Member
Joined
Apr 26, 2013
Messages
26
Office Version
  1. 365
Platform
  1. Windows
I have a range of cells that have formulas in them that give a number as a result. I want to find the highest number. When I use MAX, the end result is always 0 as it is looking at the formula and not the result.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Post some same data that we can evaluate. Use XL2BB to upload the data so that it is in a format that we can manipulate. Do not post a picture as we cannot manipulate data in a picture.
 
Upvote 0
Per alansidman, please show us an example because the MAX function will return the largest value even when considering the results of a range of formulas.
 
Upvote 0
I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
EBSDN Eisteddfod Program with formulas.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE
1Submission DateFirst NameLast NameStreet AddressStreet Address Line 2CityStatePost CodeCountryE-mailPhone NumberMobileDate of BirthAge Cutoff DateAge as at 1st Day of JanuaryUpload Birth CertificateDuo Partner Name (First Name)Duo Partner Name (Last Name)Date of Birth 2Upload Birth Certificate 2Trio Partner Name (First Name)Trio Partner Name (Last Name)Date of Birth 3Upload Birth Certificate 3Studio NameStudio MobileBabies 5/uAmount $SOLO Restricted 7/uSOLO Open 7/u7/U Duos and Trios7/U ImprovisationsAmount $ 2SOLO Restricted 9/uSOLO Open 9/u9/U Duos and Trios9/U ImprovisationsAmount $ 3SOLO Restricted 11/uSOLO Open 11/u11/U Duos and Trios11/U ImprovisationsAmount $ 4SOLO Restricted 13/uSOLO Open 13/u13/U Duos and Trios13/U ImprovisationsAmount $ 5SOLO Restricted 15/uSOLO Open 15/u15/U Duos and Trios15/U ImprovisationsAmount $ 6SOLO Restricted Open AgeSOLO Open AgeOpen Age Duos and TriosOpen Age Improvisations
2########TAHLIBORGE32 Chippendale PlaceHelensburghNSW2508Australiajacqueline.borge@det.nsw.edu.au04144986470414498647################11https://www.jotform.com/uploads/EBS...445/7B49EB2A-8AB4-4844-B575-B505C34CBFF8.jpegDance Network Engadine0448481661000Standard Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.007500
3########SavannahDigges-Lagula9/1 Perrin AvenuePlumptonNSW2761Australiabriony.d@hotmail.com04047160870404716087################8Static dance studios041122827500Hip Hop $25.00 Contemporary $25.00 Modern/Lyrical $25.00Tap $20.0095000
4########IslaHarkin14 Niger PlaceKearnsNSW2568Australiakarenharkin@bigpond.com04476209350447620935################11Tip Tap Toe Dance Company0447620935000Standard Jazz $25.00 Fast Tap $25.00Broadway Jazz $25.00 Modern/Lyrical $25.0010000
5########RosemaryFinnerty13 Sicilia StPrestonsNSW2170Australiaannaraciti@gmail.com04104991130410499113################11https://www.jotform.com/uploads/EBS...96/Finnerty_Rosemary_BirthCert_compressed.pdfRed Stage Studios0414906117000Singing Own Choice $25.00 Singing Ballad $25.00 Singing Musical Theater $25.00 Singing Disney or Pixar $25.0010000
6########LucaButler5 EucalyptusWorrigeeNSW2540Australiaanna.butler@nnpspc.org.au0416310390################9DanceWorks Nowra0422 595 81300Contemporary $25.00Classical Ballet $25.00 Standard Jazz $25.00 Broadway Jazz $25.00 Modern/Lyrical $25.00125000
7########KiaraChilby17 Stubbs RdAlbion ParkNSW2527Australiarocky80@live.com.au425758050433357272################14All that Jazz0404 834 58500000Modern/Lyrical $25.00 Dance Own Choice $25.00Hip Hop $25.00 Standard Jazz $25.00 Broadway Jazz $25.00 Contemporary $25.00 Fast Tap $25.00 Slow/Straight Tap $25.00 Mr or Miss EBSDN $35.00235
8########KeiraBonney13 Downes DriveAlbion ParkNSW2527Australiajo_jas_bonney@hotmail.com04126557220412655722################13EmilyCadden########iDance Performing Arts Studios04347008160000Jazz $35.00 Modern/Lyrical $35.00700
9########JazmineGagnuss1/184 Tongarra roadAlbion parkNsw2527Australiashazzmac@icloud.com04115765790411576579################7DDC Dance Studio0410 708 5020Modern/Lyrical $25.00250000
10########EmilyVaughan119 Shearwater DriveLake HeightsNSW2502Australiamel.mark@hotmail.com0405139095################17Joanne Grace School of Dance0413006110000000Classical Ballet $25.00 Demi Character $25.00 Broadway Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Dance Own Choice $25.00 EBSDN (Shining Stars) $25.00
11########EmilyVaughan119 Shearwater driveLake HeightsNSW2502Australiamel.mark@hotmail.com0405139095################17Joanne Grace School of Dance0413006110000000Classical Ballet $25.00 Demi Character $25.00 Broadway Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Dance Own Choice $25.00 EBSDN (Shining Stars) $25.00
12########LuaRiddle7 Cherry stWoononaNSW2517Australiasanaz.khanlari@gmail.com0403667896################10Thirroul Dance academy4.48E+08000Contemporary $25.00 Modern/Lyrical $25.00Standard Jazz $25.007500
Submissions
 
Upvote 0
EBSDN Eisteddfod Program with formulas.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE
1
2Section2Start Time
3111/u ImprovisationsJazz9:00 amSOLOSDUOS & TRIOSTimeAge GroupsRoutines
4#REF!3Babies 5/uClassical Ballet
5181LexieMustafa362SiennaSpence373SiennaSpence181LexieMustafa362SiennaSpence373SiennaSpence181LexieMustafa362SiennaSpence373SiennaSpence3SOLO Open 7/uDemi Character
6394MadilynLake odea415JessicaRobinson846NathanielDigges-Lagula394MadilynLake odea415JessicaRobinson846NathanielDigges-Lagula39&0041&MadilynLakeodea84&MadilynLake OdeaSOLO Restricted 7/uStandard Jazz
7##7AngelinaPatolo##8AngelinaPatolo##9LahniHepi##7AngelinaPatolo##8AngelinaPatolo##9LahniHepi##&00##&00##&007/u ImprovisationsHip Hop
8##10BritneyKnight##11IsabelleCampbell##12KobyOBrien##10BritneyKnight##11IsabelleCampbell##12KobyOBrien##4BritneyKnight##5IsabelleCampbell##6KobyOBrienSOLO Open 9/uBroadway Jazz
9##13JadeIrving##14SiennaAllitt    ##13JadeIrving##14SiennaAllitt    ##&JadeIrving##&00 &00SOLO Restricted 9/uContemporary
10                             &00 &009/u ImprovisationsModern/Lyrical
11Results: 1st………………..2nd………………..3rd………………..HC………………..SOLO Open 11/uFast Tap
12SOLO Restricted 11/uSlow/Straight Tap
Program
Cell Formulas
RangeFormula
E2E2=LEN(Y5)
L4L4=((6-COUNTIF(#REF!,""))*$M$4)+((6-COUNTIF(#REF!,""))*$M$4)+(6-COUNTIF(#REF!,""))*$M$4+$M$5+L1
A5:L10A5=IF(ISNUMBER(SEARCH("Duos and Trios",$C$3)),INDEX($AK5:$AV5,1,COLUMN()-COLUMN($A3)+1),INDEX($Y5:$AJ5,1,COLUMN()-COLUMN($A3)+1))
Y5Y5=SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$1))
Z5Z5=IF(AA5="","","1")
AA5:AB10AA5=IFERROR(INDEX(Submissions!B:B,$Y5),"")
AC5AC5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$2)),"")
AD5,AP5AD5=IF(AE5="","","2")
AE5:AF10AE5=IFERROR(INDEX(Submissions!B:B,$AC5),"")
AG5AG5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$3)),"")
AH5,AT5AH5=IF(AI5="","","3")
AI5:AJ10AI5=IFERROR(INDEX(Submissions!B:B,$AG5),"")
AK5AK5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($M$1)),"")
AL5AL5=IF(AM5="","","1")
AM5,AQ5,AU5,AQ8,AU8,AM8:AM10AM5=IFERROR(INDEX(Submissions!$B:$B,AK5),"")
AN5,AR5,AV5,AR8,AV8,AN8:AN10AN5=IFERROR(INDEX(Submissions!$C:$C,AK5),"")
AO5AO5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$2)),"")
AS5AS5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$3)),"")
Y6Y6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$4)),"")
Z6Z6=IF(AA6="","","4")
AC6AC6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$5)),"")
AD6,AP8AD6=IF(AE6="","","5")
AG6AG6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$6)),"")
AH6,AT8AH6=IF(AI6="","","6")
AK6AK6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($M$4)),"")
AL6:AL7,AP6:AP7,AT6:AT7,AL9:AL10,AP9:AP10,AT9:AT10AL6=IF(AM6="","","&")
AM6,AQ6,AU6,AQ9,AU9AM6=IFERROR(INDEX(Submissions!$Q:$Q,AK5),"")
AN6,AR6,AV6,AR9,AV9AN6=IFERROR(INDEX(Submissions!$R:$R,AK5),"")
AO6AO6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$5)),"")
AS6AS6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$6)),"")
Y7Y7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$7)),"")
Z7Z7=IF(AA7="","","7")
AC7AC7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$8)),"")
AD7AD7=IF(AE7="","","8")
AG7AG7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$9)),"")
AH7AH7=IF(AI7="","","9")
AK7AK7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($M$7)),"")
AM7,AQ7,AU7,AQ10,AU10AM7=IFERROR(INDEX(Submissions!$U:$U,AK5),"")
AN7,AR7,AV7,AR10,AV10AN7=IFERROR(INDEX(Submissions!$V:$V,AK5),"")
AO7AO7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$8)),"")
AS7AS7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$9)),"")
Y8Y8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$10)),"")
Z8Z8=IF(AA8="","","10")
AC8AC8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$11)),"")
AD8AD8=IF(AE8="","","11")
AG8AG8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$12)),"")
AH8AH8=IF(AI8="","","12")
AK8AK8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($M$10)),"")
AL8AL8=IF(AM8="","","4")
AO8AO8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$11)),"")
AS8AS8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$12)),"")
Y9Y9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$13)),"")
Z9Z9=IF(AA9="","","13")
AC9AC9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$14)),"")
AD9AD9=IF(AE9="","","14")
AG9AG9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$15)),"")
AH9AH9=IF(AI9="","","15")
AK9AK9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($M$13)),"")
AO9AO9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$14)),"")
AS9AS9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$15)),"")
Y10Y10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$16)),"")
Z10Z10=IF(AA10="","","16")
AC10AC10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$17)),"")
AD10AD10=IF(AE10="","","17")
AG10AG10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$18)),"")
AH10AH10=IF(AI10="","","18")
AK10AK10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($M$16)),"")
AO10AO10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$17)),"")
AS10AS10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H$3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C$3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$18)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
O3:S3List=$AX$4:$AX$29
T3:W3List=$BA$4:$BA$25
C3:G3List=$AX$4:$AX$29
H3:K3List=$BA$4:$BA$25
 
Upvote 0
The first mini sheet is part of the 2nd mini sheet. In the 2nd mini sheet i want to find the highest value of the following ranges B:5 to B:10, F:5 to F:10 and J:5 to J:10 .
 
Upvote 0
The ranges you mention don't seem to hold formulas ??
But do you want the MAX of the combined ranges ?
Excel Formula:
=MAX(B5:B10,F5:F10,J5:J10)
 
Upvote 0
They have the formulas {=IF(ISNUMBER(SEARCH("Duos and Trios",$C$3)),INDEX($AK5:$AV5,1,COLUMN()-COLUMN($A3)+1),INDEX($Y5:$AJ5,1,COLUMN()-COLUMN($A3)+1))} in each cell. The MAX function only gives a 0 result.
 
Upvote 0
Even with the formulas in place the range still returns a result
See cells B13, F13, I13
Otherwise I must be not understanding your question ?
Cell Formulas
RangeFormula
B5:B9B5=IF(ISNUMBER(SEARCH("Duos and Trios",$C$3)),INDEX($AK5:$AV5,1,COLUMN()-COLUMN($A3)+1),INDEX($Y5:$AJ5,1,COLUMN()-COLUMN($A3)+1))
B13,I13:J13,F13B13=MAX(B5:B10)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,214,924
Messages
6,122,293
Members
449,077
Latest member
Rkmenon

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top