Lookup a value in a table

steve1123

Board Regular
Joined
Nov 1, 2012
Messages
74
Hello,

I have a predicament regarding a table that I'm trying to solve.

There is a table with Weights along the left side, ISA Temperature Deviation along the top, and Anti-Ice correction along the bottom.

For a given weight, ISA Dev, and whether or not the Anti-ice system is on, an airplane can reach a certain max altitude, which this table will show you. Using a combination of VLookup, HLookup, and Match functions, I have already put together a formula to compute the max altitude for a given weight, ISA deviation, and Anti-ice status.

The Anti-ice system takes its power from the engines, so if the Anti-ice system is on, the aircraft cannot fly as high, hence the negative corrections if the system is on.

Now, the problem. There are some situations where a pilot may want to know, for a given ISA dev and Anti-ice status, the highest weight the aircraft can be at in order to reach a specified altitude. If the aircraft is too heavy at a given time, as the fuel is burned, the aircraft may get down to the required weight, at which point the aircraft can climb to the requested altitude.

Essentially, the 4 variables are Weight, ISA Dev, Altitude, and whether or not the Anti-Ice system is on. I need a way to "read" the table when the known values are ISA Dev, Altitude, and Anti-ice status on/off. The flow of the formula should go something like this:

If the Anti-ice system is not on:
1) Based on user input, ISA Dev, go to the column for the current ISA Dev.
2) Based on user input, Desired Altitude, go down that column to find the bottom-most instance of that altitude.
3) Move left to find the weight. This will represent the heaviest the aircraft can be an still reach and maintain that altitude.

I have attached the chart. The cell containing Max Altitude is A1. I have the fields for user input at the top of the chart for simplicity. The formula should be in G2.

For example, if the Anti-ice system is not on:
ISA Dev: -5
Anti-ice on?: No
Desired Altitude: 36000ft
Result should be: 51000Kg

If the Anti-ice system is on, this requires some extra work:
1) Take the given requested altitude and add the correction value at the bottom of the chart. In the example, 36000 + 5000 = 41000Kg
2) Repeat steps 1-3 above, essentially replacing 36000, with 41000 (since you have to subtract the 5000ft correction from 41000ft, in order to maintain the requested 36000ft).

In the above example, if the Anti-ice system is on, the result should be 39000Kg.

The main thing to keep in mind is that the service ceiling is 41000ft, so no matter what, the assumed altitude cannot go past 41000. If a pilot is requesting an altitude that cannot be reached at any weight, the result should produce an "Unable" response.

Thanks in advance for any assistance. I know this one is rather complicated. It's been stumping me for a while.

[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Max Altitude[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]ISA Dev[/TD]
[TD="class: xl66, width: 64"]Req Alt[/TD]
[TD="class: xl66, width: 64"]Anti-ice
on?[/TD]
[TD="class: xl66, width: 64"]Weight[/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl67"]WEIGHT[/TD]
[TD="class: xl65"]ISA DEVIATION (C)[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]38000[/TD]
[TD="class: xl66"]NO[/TD]
[TD="class: xl66"]RESULT
HERE[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl67"][/TD]
[TD="class: xl65, align: right"]-15[/TD]
[TD="class: xl65, align: right"]-10[/TD]
[TD="class: xl65, align: right"]-5[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65, align: right"]15[/TD]
[TD="class: xl65, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]30000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]31000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]32000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]33000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]34000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]35000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]36000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]37000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]38000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]39000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]41000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]40000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]41000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]40000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]42000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]43000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]39000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]44000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]45000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]38000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]46000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]47000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]48000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]37000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]33000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]49000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]33000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]50000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]32000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]51000[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]36000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]31000[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]51800[/TD]
[TD="class: xl68, align: right"]29000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]35000[/TD]
[TD="class: xl68, align: right"]34000[/TD]
[TD="class: xl68, align: right"]31000[/TD]
[/TR]
[TR]
[TD="class: xl65"]Correction
ENG & WNG
ANTI-ICE ON[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]-3000[/TD]
[TD="class: xl68, align: right"]-5000[/TD]
[TD="class: xl68, align: right"]-5000[/TD]
[TD="class: xl68, align: right"]-5000[/TD]
[TD="class: xl68, align: right"]-5000[/TD]
[TD="class: xl68, align: right"]-8000[/TD]
[TD="class: xl68, align: right"]-10000[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Here is my take on it:

=IFERROR(LOOKUP(2,1/(INDEX(B4:I27,0,MATCH(E2,B3:I3))=F2-HLOOKUP(E2,B3:I27,25,0)*(G2<>"NO")),A4:A26),"Unable")

and some (very limited) test results:

-5 / 36000 / No: my formula 51800; Peter's formula "Unable"
20 / 21000 / Yes: my formula 51800; Peter's formula 51000
Hi Tetra

I'm not sure where those blue results come from? For me your formula returns 51000 and so does mine (it is the exact example shown in post 4)

The red example is one similar to what Marcelo is raising. 20 / 21000 is not a value available in the table so I don't know if it is a combination that is allowed or not. Given that your example was for "Yes" and 20/31000 is in the table it may well be valid. However, rather than speculate on what seems reasonable to us & adjust the formula to accommodate, I think we need the OP to clarify that issue. Your red example, though, did highlight a typo in my formula - I was one row short in one of my ranges, so it should have been as shown below which results in both our formulas returning the same result for the red example. :)
=IFERROR(INDEX(A4:A26,AGGREGATE(14,6,(ROW(A4:A26)-ROW(A4)+1)/((B4:I26=F2-(B3:I3=E2)*(B27:I27)*(G2="YES"))*(B3:I3=E2)),1)),"Unable")


Yes, if we strictly follow this sentence the formula must return Unable. But this, IMHO, can cause some confusion.
I was thinking in real life: could not the pilot ask for a lower altitude than those on the table? After all, 32,000 feet seems a reasonable altitude for a modern aircraft.
Another advantage in using greater or equal is that requests for non-exact altitudes such as 35500 would be accepted and treated as the value immediately above (36000).

M.
Marcelo, your argument about altitudes below the bottom row of the table sound reasonable, but as I said above, it would be better for the OP to clarify than for us to guess.
Similarly with your 35500 example, I'm not sure if your suggestion is an advantage or a disadvantage. It could be quite reasonable, but it could also be that such an entry is not allowed (in some columns at least) and "Unable" might highlight the incorrect entry rather than return the result from a nearby row. I just don't know.
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
... I'm not sure where those blue results come from? For me your formula returns 51000 and so does mine (it is the exact example shown in post 4)...
It was a typo in my post -- those results were for -5 / 35000/ No.

Now that you have corrected the range in your formula, it works OK.
Yet, mine has fewer reference calls ;)
 
Upvote 0
Wow! Thank-you very much for the fast and thorough answers!

I'm just inbetween flights and don't have my computer readily available at the moment, so I can't check the formulas just yet, but I will as soon as I can.

Hopefully I can clarify some of the issues in the meantime.

1) 32000ft is indeed fairly common for the modern jet. However, there are instances, usually either due to weather or traffic constraints, where the pilot or air traffic control may be looking for the aircraft to climb to a higher altitude.

2) For the example of +20 and a requested altitude of 21000ft with the anti-ice ON, the result should be 51800Kg.
After adding the anti-ice correction factor of 10000ft, the user (or in this case the formula) must use an "effective" altitude (for lack of a better word, the altitude that must be found in the table) of 31000ft. The highest allowable weight at this altitude is 51800ft. As you can see, high ISA Dev with AI ON can be very limiting when getting into the higher altitudes.

The above is a more extreme example, but nevertheless one that would have to compute properly.

Typically, when the anti-ice is required, the ISA will be lower. So a more common example would be:
ISA: -5
AI: ON
Requested alt: 35000ft
The expected result should be 41000Kg
The flow would be: 35000ft, plus the AI correction of 5000ft = 40000ft, this is the "effective" altitude that we must go looking for in the table, within the ISA -5 column, and the highest allowable weight at 40000ft is 41000Kg.

3) Altitudes not listed on the chart.
This chart is typically used for higher altitudes. So if a requested altitude is "below" the lowest value on the chart, the computed result should be 51800Kg, since the aircraft can reach those lower altitudes at the max gross weight. This should be fairly straight forward when the AI system is OFF. However, if the AI is ON, a similar flow should still apply, only now with the "effective" altitude.
For example:
ISA: 0
AI: ON
Requested alt: 32000ft.
Computed result should be 48000Kg
So, the initial requested alt of 32000ft is "below" the lowest value on the chart for an ISA Dev of 0. However, after adding the AI correction of 5000ft, we now have to look for an "effective" altitude of 37000ft, and the highest allowable weight at that altitude would be 48000Kg.

4) No need to worry about non-whole thousands of ft, as we typically fly only at whole thousands of ft. I'll be using either data validation or a drop-down list to restrict the input.

5) One other thing I should mention is that I cant use VBA, as I will ultimately be looking to convert this excel file to an iOS app. Not that this has been an issue with the proposed solutions. I'm looking for suggestions on how to convert this to an app as well, but I'm thinking I should probably start another thread for that. So far, Open as App looks like the best option I've found, though it may be cost-limiting.

I hope this helps to clarify a confusing table and situation. Thank-you once again for all of your help, patience, and continued support. I look forward to checking out the formulas hopefully later tonight!
 
Upvote 0
Hopefully I can clarify some of the issues in the meantime.

2) For the example of +20 and a requested altitude of 21000ft with the anti-ice ON, the result should be 51800Kg.

So, it seems to me logical that with Anti-Ice "NO", the formula should also return 51800, not Unable.
If I am right, it is necessary to change the comparison from equal to greater or equal (see post # 6).

M.
 
Upvote 0
Yes that sounds correct. If it's the formula is showing it could maintain the requested altitude at a higher weight, then the answer should default to a max of 51800Kg, as that is the maximum weight allowable as per aircraft structural limitations

I'm hoping this clarifies the issue in question? Or did I misunderstand?
 
Last edited:
Upvote 0
I'm hoping this clarifies the issue in question?
It certainly clarifies some things for me. Based on the additional information, here are two updated options for you to consider.

Excel Workbook
ABCDEFGHI
1Max AltitudeISA DevReq AltAnti-iceWeightWeight
2WEIGHTISA DEVIATION (C)1532000yes3900039000
3-15-10-505101520
4300002900034000410004100041000410004100041000
5310002900034000410004100041000410004100041000
6320002900034000410004100041000410004100041000
7330002900034000410004100041000410004100041000
8340002900034000410004100041000410004100041000
9350002900034000410004100041000410004100040000
10360002900034000410004100041000410004100040000
11370002900034000410004100041000410004100039000
12380002900034000410004100041000410004000039000
13390002900034000410004100040000400004000038000
14400002900034000400004000040000400003900038000
15410002900034000400004000040000400003900037000
16420002900034000390003900039000390003800037000
17430002900034000390003900039000390003800037000
18440002900034000380003800038000380003800036000
19450002900034000380003800038000380003700036000
20460002900034000370003700037000370003700035000
21470002900034000370003700037000370003600034000
22480002900034000370003700037000370003600033000
23490002900034000360003600036000360003500033000
24500002900034000360003600036000360003500032000
25510002900034000360003500035000350003400031000
26518002900034000350003500035000350003400031000
27Correction0-3000-5000-5000-5000-5000-8000-10000
Flight Calc
 
Last edited:
Upvote 0
Spreadsheet Formulas
CellFormula
H2=IFERROR(AGGREGATE(14,6,A4:A26/(INDEX(B4:I26,0,E2/5+4)>=F2-INDEX(B27:I27,E2/5+4)*(G2="yes")),1),"Unable")
I2=IFERROR(-LOOKUP(0,-A4:A26/(INDEX(B4:I26,0,MATCH(E2,B3:I3))>=F2-HLOOKUP(E2,B3:I27,25)*(G2<>"no"))),"Unable")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Based on my testing so far, both of these formulas work flawlessly! Thank-you very much for everyone's hard work and assistance on this one.

While they both work perfectly in Excel, I've run into a problem implementing them through Open As App. I think the software has a problem with how the formula works, in that it seems to rely on a DIV/0! calculation, and it simply takes the highest weight before a result of DIV/0! is returned. In any case, that's not a fault of the formula, but rather the software of Open As App not being able to compute it properly. I have emailed their technical support department to see if they have any recommendations.

Thanks again!
 
Upvote 0
Based on my testing so far, both of these formulas work flawlessly! Thank-you very much for everyone's hard work and assistance on this one.
You are welcome. It was something of a team effort. :)
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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