Help with a Fromula that finds the largest value in a Range and then....

Hi Experts, this one has got me stumped. The attached sample workbook should explain what I'd like.

In short, I want to create a pseduo-solver type equation for a resource allocation problem. Ala, I want the the largest values in range G:AA to determine what Aircraft Type the Flight Hours value in column F should go to. A method to mitigate ties in Range G:AA is explained in the workbook.

Hoping you can help me save a lot of time here.
SampleWorkbook.xlsx
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
"Solution Cells (in yellow) find the Largest Value in Range G:AA (signifying the Aircraft most "Fit" to perform the Flight Hours) THEN place the flight hours amount for that record ID on the aircraft type with the Largest Value. If there is a tie for largest value in G:AA the tie should go to the aircraft with the lower operating cost per hour (See Table1 column B). Please do not transpose aircraft types in the tables, they mimic the real workbook."

I think I know what you are asking, although your wording is ambiguous; especially regarding "G:AA".

Just for clarity, please can you advise what figure you are expecting to see in cell [ C2 ], & explain how that value was selected?

Thank you.
-PolakAuthor Commented:
Sure,

Let's say that aircraft A was both the cheapest to operate and the most fit aircraft for all of the records in table 2. As such the sum of table2[flight hours] would go exclusively in cell c2. If aircraft A and B were equally as fit to perform all of the records the sum of table2[flight hours] would still go on aircraft A because it is cheaper to operate.


In terms of the workbook I attached record 1's 281 hours would go on Aircraft type N because there are no ties.
[ fanpages ]IT Services ConsultantCommented:
Thanks.

However, to repeat my query:

Just for clarity, please can you advise what figure you are expecting to see in cell [ C2 ], & explain how that value was selected?

I don't think 281 (hours) is to be found in cell [ C2 ] from your reply.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

-PolakAuthor Commented:
c2 should be the sum of flight hours from table2 in which aircraft A was the most fit aircraft to perform an individual record.  Ties managed as discussed above.

The values  for table2[a:u] (regular range g:aa) were random so I don't know where the above is true.
-PolakAuthor Commented:
Looking at it by eye (on my phone) it appears aircraft A is the most fit only in one record (record is 20) therefore c2 would be 301 hours.
[ fanpages ]IT Services ConsultantCommented:
Hi again,

I am still a little confused about what you are looking for.

If we ignore "tied" results for the time being, I have placed the following formula in cell [C2]:

=INDEX($F$3:$F$22,MATCH(MAX(OFFSET($F$3:$F$22,0,MATCH($A2,Table2[[#Headers],[A]:[U]]))),OFFSET($F$3:$F$22,0,MATCH(A2,Table2[[#Headers],[A]:[U]])),0))

Open in new window


I have copied this down from cell [C2] to cell [C22].

The results I see are as follows:

[C2]: 301 (for Aircraft Type "A")
[C3]: 281 (for "B")
[C4]: 311 (for "C")
[C5]: 311 (for "D")
.
.
.
[C22]: 458 (for "U")


However, I really do not know if these figures are what you were expecting.

The formula in column [C] finds the relevant column in the range [G2:AA2] that matches the "Aircraft Type" stored in corresponding column [A].

Within the appropriate column, the maximum value is located, then the value for "Fight Hours" (column [F]) is retrieved.

However, I am lost what you are meaning by "most fit", & what the relevance of column [ B ], "CostperHour" has with the calculation (if at all).


Perhaps if you describe, in plain English, how to reach the value required (if I have missed the mark so far), I (or any other Expert) may have a better chance of providing what you require.

Also, a description of what to do (in plain English) with a "tied" scenario would be helpful.

Thank you.

I have attached your original workbook with the above formula applied.
Q-28696999.xlsx
-PolakAuthor Commented:
Ignoring ties; there is something up with  the formula. If we look at the 281 hours from Record ID: 1 they were incorrectly placed on Aircraft B in cell C3. The MAX value for ID1 was Aircraft Type M value of 24; Aircraft B was a value of 23.

Regarding ties.... in plain English.... the values in table2 are a measure of "Fit" to perform the Flight Hours. (Lets pretend that the only Fit we were measuring was an Aircraft's ability to Hover). Table1[[CostperHour]] measures the costs to perform flight hours (by the hour). So.... knowing this ask yourself the question:

"If I have 2 aircraft that can hover equally as well, then where should I allocate the flight hours they'll be Hovering?"
Answer: "to the aircraft that can do it cheaper" Ala, ties are given to the aircraft that has the lower cost per hour.

PS I've used Index(Match(Match before but never nested an Offset, in the real workbook these two tables are on separate worksheets so I'd like to use column headers avoid the Offset unless absolutely necessary.
-PolakAuthor Commented:
I've solved for the records with orange Flight Hours Amounts.

Note1 Record 2 had a 3 way tie between the green colored cells, the flight hour value for record 2 went to the aircraft with the lower cost per hour.

Note2 that Aircraft F was the highest value for more than 1 record, therefore the value is a Sum of flight hours from the records at which it was highest.
OrangeRecordsManuallySovled.xlsx
[ fanpages ]IT Services ConsultantCommented:
"Ignoring ties; there is something up with  the formula. If we look at the 281 hours from Record ID: 1 they were incorrectly placed on Aircraft B in cell C3. The MAX value for ID1 was Aircraft Type M value of 24; Aircraft B was a value of 23."

The value of 23 in cell [H3] is one of the (& the first of the) largest values in column [H] (for Aircraft Type "B"), so that is selected, & the corresponding value in column [F] ("Flight Hours") is 281 (i.e. cell [F3]) so that value is transposed to cell [C3].

If that logic not what you intended?

The rest of your reply still uses terms like "fit", & "hover"; these are still alien to me as I am not involved in your industry.  That is what I meant by "plain English".  You seem to continue using "plane English" :)

Also, regarding your postscript about using different worksheets.  Please provide the necessary sample workbook to match your exact requirements.  It makes any attempt to help you, so much easier!
-PolakAuthor Commented:
The value of 23 in cell [H3] is one of the (& the first of the) largest values in column [H] (for Aircraft Type "B"), so that is selected, & the corresponding value in column [F] ("Flight Hours") is 281 (i.e. cell [F3]) so that value is transposed to cell [C3].

No, pivot your thinking. I'm interested in the largest value running horizontally across the Record ID.  

Did the solved 3/4 record IDs in the workbook I attached clarify things for you?
[ fanpages ]IT Services ConsultantCommented:
Sorry, I am really lost (again).

What is the correlation between cell [C2] & looking across the range [G3:AA3] (i.e. Record ID: 1)?

...and, no, your workbook did not give me any guidance, I am sorry to say.  Not only could I not match why the figures in cells [C7], [C9], & [C14] were there, I did not understand your colour-coding... & I don't think that is because I have Protanomaly & Deuteranomaly colour-vision deficiency!

Seriously, I must be missing something obvious if all the data is there.

Queries:

a) Why do you want to look across rows, rather than down columns, of data within "Table 2"?
b) How do the "Aircraft Types" in column [A] (of "Table 1") relate to the Record IDs (in column [E]; "Table 2")?
c) How are the values in column [C] (of "Table 1") "Sum of Flight Hours" calculated?

I think column [ B ] "CostperHour" may have relevance if more than one match is found in "Table 2", but adding that into the reply (now) may well confuse the issue I am having resolving the basic requirements.

If I am still at a loss after your further explanation, then I think leaving this question to somebody else may well be the best course of action.
-PolakAuthor Commented:
a) Why do you want to look across rows, rather than down columns, of data within "Table 2"?
-Because each value going across the row is how well an aircraft performs that Record ID I care about comparing how an aircraft performs vs its replacement aircraft on each record ID.

b) How do the "Aircraft Types" in column [A] (of "Table 1") relate to the Record IDs (in column [E]; "Table 2")?
-They are the same aircraft, hence they have the same letter.

c) How are the values in column [C] (of "Table 1") "Sum of Flight Hours" calculated?
-I've drawn some arrows in my workbook to help illustrate how the values got there. Hoping that we can Fisher Price this solution.
SolutionWithArrows.xlsx
-PolakAuthor Commented:
I fixed the "corrupt" attachment, not sure if you were having issues with it....
-PolakAuthor Commented:
Lol... any better?
-PolakAuthor Commented:
Anyone else wanna participate on this one i think my workbook with arrows scared fanpages into hiding.
[ fanpages ]IT Services ConsultantCommented:
(Comment 2015-07-15 at 21:22:22 ID: 40883779)

"If I am still at a loss after your further explanation, then I think leaving this question to somebody else may well be the best course of action. "

Sorry, I am still confused.

Please re-request attention to prompt any other willing party to assist.

Good luck.
Saurabh Singh TeotiaCommented:
Polak,

Their you go i wrote a udf for your problem to do what you are looking for.. Now if you compare the results which i'm getting with the results you are displaying + an comparison of hours which i did it's matching and it gives you what you are looking for..

Function gethours(r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range)
    Application.Volatile
    Dim cell As Range, fcol As Long, lcol As Long, frow As Long
    Dim vl As Long, cp As Long, np As Long, rng As Range, rng1 As Range, l As Long, k As Long
    Dim fr As Long, lr As Long, z As String
    fcol = r4.Column
    lcol = r4.Columns.Count + fcol - 1
    fr = r4.Row
    lr = r4.Rows.Count + fr - 1
    frow = r3.Row
    cp = 99999999
    For l = fr To lr
        Set rng = Range(Cells(l, fcol).Address & ":" & Cells(l, lcol).Address)
        vl = Application.WorksheetFunction.Max(rng)
        If Application.WorksheetFunction.CountIf(rng, vl) > 1 Then
            For k = fcol To lcol
                If Cells(l, k).Value = vl Then
                    np = Application.WorksheetFunction.VLookup(Cells(frow, k).Value, r1, 2, 0)

                    If np < cp Then
                        cp = np
                        z = Trim(UCase(Cells(frow, k).Value))
                    End If
                End If
            Next k
            If UCase(Trim(r.Value)) = z Then gethours = gethours + Cells(l, r2.Column).Value
        Else
            For k = fcol To lcol
                If Cells(l, k).Value = vl Then
                    If UCase(Trim(r.Value)) = Trim(UCase(Cells(frow, k).Value)) Then gethours = gethours + Cells(l, r2.Column).Value
                End If
            Next k

        End If

    Next l

End Function

Open in new window


Your workbook after Solution...

Saurabh...
SolutionWithArrows.xlsm
-PolakAuthor Commented:
Ah ha! Glad I my talking in circles made sense to some one. Okay so doing some light testing I noticed an issue... go to Cell S8 in your workbook and increase the value to 25. You'll see that the flight hours for Aircraft L have increased to 2078 (Odd, given the record IDs FH Value is only 270). Even weirder is if after increasing to 25 we decrease to 0 it seems to modify the Values of Two other Aircraft simultaneously.... that shouldn't ever happy since if a "winner" of FH becomes a "loser" all the hours should only go to the next highest rated aircraft "in line".

I wish I could appreciate how cool your UDF solutions are, but they frequently go WAY over my head. That said I'm still using a unconcatenate and matrix lookup UDF you wrote me a while back and I love it.

However, how flexible is this UDF going to be? for example if in the really workbook table1 is on a different worksheet from table 2, or perhaps the record ID column is not directly to the left of the "fit" values, or perhaps a real Aircraft type has unusual characters in it "/" "-" etc... is any of that going to be an issue ? perhaps we can step through the code so I can know it's limitations?
[ fanpages ]IT Services ConsultantCommented:
"Been looking for that button, hasn't popped up yet. If you wanna message me some contact info of some sort I'm happy to explain in another conversation medium if you think that will help you. I don't think that this is as complicated as you think/we are making it."

No, thanks.  Your condescending attitude may continue.
-PolakAuthor Commented:
Woah now. I think we've miscommunicated on more than just some spreadsheets. I'm not sure what I said that was condescending; perhaps it was my Plane English...., but nothing said here was meant to be inflammatory, sorry that it was interpreted that way.
[ fanpages ]IT Services ConsultantCommented:
OK, if that was not your intention, I apologise.

You have another party engaged with the project now, so I hope you will reach the required result soon.
-PolakAuthor Commented:
Not at all, was just trying to get some attention from another expert & see if you had truly given up. Cheers.
[ fanpages ]IT Services ConsultantCommented:
I presume you are aware that the "Experts" are not paid staff, but volunteers participating in questions in their free time.  Volunteers that have their own commitments & priorities outside of the site, of course.

The fact we are in different time zones (me in the UK, & you in the United States) is also not conducive to providing expedient exchanges & resolutions either.

The medium of text-based communication is, as you probably realised not ideal to relay information, as the word of one party can easily be misinterpreted by another using localised turns of phrase, & jargon specific to their experience/knowledge.

Even if I cannot help directly, I will often try to establish more facts/re-wording to entice other volunteers to participate, but also to ascertain why a problem is seemingly being ignored.  It seems that approach finally prompted the interest of another party.

We obviously didn't meet on the same terms in this question, but hopefully that will not always be the case.

Hope you have a pleasant day.
Saurabh Singh TeotiaCommented:
Polak,

I notice now what you are try to do and their was a small thing that i didn't do since i had to take the assumption in loop and once i do that vola is fixed...

Polak always happy to help here and glad i have been able to do so in the past and yeah UDF is my logical mind i have written in formula...

Now couple of things for your answer on this formula...

1. It doesn't matter if your air type and cost per hour is in different sheet the UDF will still work.
2. It doesn't matter how you aircraft names are..till the time both are identical for both table-1 and table-2 it will match and work.
3. it doesn't matter the flight hours are on the right side or on the left side till the time you define that range correctly it will automatically pick those flight hours.
4. The only thing you need to be constraint about in table1 that aircraft type should be the first column and cost per hour should be second since i have used vlookup formula to determine cost per hour in case of tie.
5. I have assumed your cost per hour won't got more then 99999999 in this line cp = 99999999, However if it goes more than this..Then accordingly you need to change it..

Enclosed is the revised UDF and Workbook...

Function gethours(r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range)

    Dim cell As Range, fcol As Long, lcol As Long, frow As Long
    Dim vl As Long, cp As Long, np As Long, rng As Range, rng1 As Range, l As Long, k As Long
    Dim fr As Long, lr As Long, z As String
    fcol = r4.Column
    lcol = r4.Columns.Count + fcol - 1
    fr = r4.Row
    lr = r4.Rows.Count + fr - 1
    frow = r3.Row
    
    For l = fr To lr
    cp = 99999999
        Set rng = Range(Cells(l, fcol).Address & ":" & Cells(l, lcol).Address)
        vl = Application.WorksheetFunction.Max(rng)
        If Application.WorksheetFunction.CountIf(rng, vl) > 1 Then
            For k = fcol To lcol
                If Cells(l, k).Value = vl Then
                    np = Application.WorksheetFunction.VLookup(Cells(frow, k).Value, r1, 2, 0)

                    If np < cp Then
                        cp = np
                        z = Trim(UCase(Cells(frow, k).Value))
                    End If
                End If
            Next k
            If UCase(Trim(r.Value)) = z Then gethours = gethours + Cells(l, r2.Column).Value
            z = ""
        Else
            For k = fcol To lcol
                If Cells(l, k).Value = vl Then
                    If UCase(Trim(r.Value)) = Trim(UCase(Cells(frow, k).Value)) Then gethours = gethours + Cells(l, r2.Column).Value
                End If
            Next k

        End If

    Next l

End Function

Open in new window


Saurabh...
SolutionWithArrows.xlsm
-PolakAuthor Commented:
Saurabh, just a heads up I haven't had a chance yet to incorporate and test in my real workbook, but I haven't forgotten. Also, good to know on points 1-5. If an aircraft is unavailable in a specific location in my workbook it's cost actually becomes 99999999999. So I will be modifying like you said! Thanks!
Saurabh Singh TeotiaCommented:
Sure Polak..and yeah you need to make changes accordingly basis of your data that you have...
-PolakAuthor Commented:
Hi Saurabh, I've having a little trouble adapting your UDF to the real workbook. To Troubleshoot lets go back to your working solution first.

If you take Table2 in the working solution and move it to a new sheet (Even pasting it in Cell F2 on the new sheet) the UDF ceases find the largest value and mitigate ties.

To verify that we not only fix this in your most recently posted solution, but also in the real workbook, I've  duplicated the real workbooks slight differences in table positions / structure in the attached.  If possible, I'd like for us to have a column between Aircraft type and CPFH (cost per flight hour). I know you had mentioned in #4 above that you were using a vlookup and those had to be next to eachother. In the real workbook the solve of the UDF should be inbetween CPFH and Aircraft Type; however, if it's too much trouble I can live with moving CPFH next to Aircraft type. Please see the attached.
Saurabh-testingSolution.xlsm
Saurabh Singh TeotiaCommented:
Polak,

Here is the revised code since you moved this to a different sheet i added a sheet reference and it gives the desired result again what you are looking for...

Function gethours(r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range)

    Dim cell As Range, fcol As Long, lcol As Long, frow As Long
    Dim vl As Long, cp As Long, np As Long, rng As Range, rng1 As Range, l As Long, k As Long
    Dim fr As Long, lr As Long, z As String
    Dim ws As Worksheet
    Set ws = Sheets("RealTable2")
    fcol = r4.Column
    lcol = r4.Columns.Count + fcol - 1
    fr = r4.Row
    lr = r4.Rows.Count + fr - 1
    frow = r3.Row
    
    For l = fr To lr
    cp = 99999999
        Set rng = ws.Range(Cells(l, fcol).Address & ":" & Cells(l, lcol).Address)
        vl = Application.WorksheetFunction.Max(rng)
        If Application.WorksheetFunction.CountIf(rng, vl) > 1 Then
            For k = fcol To lcol
                If ws.Cells(l, k).Value = vl Then
                    np = Application.WorksheetFunction.VLookup(ws.Cells(frow, k).Value, r1, 2, 0)

                    If np < cp Then
                        cp = np
                        z = Trim(UCase(ws.Cells(frow, k).Value))
                    End If
                End If
            Next k
            If UCase(Trim(r.Value)) = z Then gethours = gethours + ws.Cells(l, r2.Column).Value
            z = ""
        Else
            For k = fcol To lcol
                If ws.Cells(l, k).Value = vl Then
                    If UCase(Trim(r.Value)) = Trim(UCase(ws.Cells(frow, k).Value)) Then gethours = gethours + ws.Cells(l, r2.Column).Value
                End If
            Next k

        End If

    Next l

End Function

Open in new window


Workbook...

Saurabh...
Saurabh-testingSolution.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Awesome Saurabh, thanks for all the help!
Saurabh Singh TeotiaCommented:
Yw..Polak Happy to Help.. :-)

Saurabh...
-PolakAuthor Commented:
PS I changed Application.WorksheetFunction.VLookup(ws.Cells(frow, k).Value, r1, 2, 0) to
Application.WorksheetFunction.VLookup(ws.Cells(frow, k).Value, r1, 3, 0) so that CPFH didn't have to be right next to Aircraft Type. Wish I could understand the rest of it tho! thx again.
Saurabh Singh TeotiaCommented:
Polak,

I don't see any problem in their till the time your r1 range has 3 columns since this is simple vlookup formula and all the logics of vlookup stands true here..

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.