Correcting array formula --- ????

[spydey]

I have a two part formula that has work wonderfully. I am not updating it to do a bit more work for me.

I have reconciled the 1st part and it is functioning as expected. The second part is the issue. I cannot seem to get it to work correctly.

The formula before making adjustments is as follows:

=IF(OR(F3=0,AND(H3:L3="N/A")),"N/A",SUM(IF(H3:L3<>"N/A",C3*(H3:L3*{0.4,0.05,0.25,0.1,0.2})))/(C3-SUM(C3*((H3:L3="N/A")*{0.4,0.05,0.25,0.1,0.2}))))

It spits out a ratio as needed and is presented as a percentage.

I have this formula filled down from D3 through D6.

Works great!

In E3, I am trying to get an overall ratio of all the items.

I can get the upper portion with the following formula:

=SUM(IF(H3:L6<>"N/A",C3:C6*(H3:L6*{0.4,0.05,0.25,0.1,0.2})))

But the lower portion is causing me a headache.

This is what I have so far and it is not working:

=(C3:C6-SUM(C3:C6*((H3:L6="N/A")*{0.4,0.05,0.25,0.1,0.2}))

In essence, the end result would be something like this:

=IF(OR(AND(F3:F6=0),AND(H3:L3="N/A")),"N/A",SUM(IF(H3:L6<>"N/A",C3:C6*(H3:L6*{0.4,0.05,0.25,0.1,0.2})))/(C3:C6-SUM(C3:C6*((H3:L6="N/A")*{0.4,0.05,0.25,0.1,0.2})))

I have been confirming all formulas with CSE, but I am at a loss as to what I am doing incorrectly for that lower portion of the ration formula.

Any ideas, thoughts, pointers, would be very very VERY appreciated!! Thank you

-Spydey

P.S. C3:C6 contain the following items: 30, 15, 5, & 50.

 

[spydey]

Sorry, should have proof read first:

I AM​ updating it .....

 

[Eric W]

First, if you look at your original formula, and write out the ratio algebraically, you will see that the C3 value is irrelevant: it cancels out. You can remove it in your formula, or replace it with a 1, and get this version:

=IF(OR(F3=0,AND(H3:L3="N/A")),"N/A",SUM(IF(H3:L3<>"N/A",H3:L3,0)*{0.4,0.05,0.25,0.1,0.2})/(1-SUM(IF(H3:L3="N/A",1,0)*{0.4,0.05,0.25,0.1,0.2})))
with CSE.

Given that, your combined version might look something like this:

=(SUM(IF(H3:L6="N/A",0,H3:L6)*{0.4,0.05,0.25,0.1,0.2}))/(4-SUM(IF(H3:L6="N/A",1,0)*{0.4,0.05,0.25,0.1,0.2}))
with CSE.

I get an answer, but you'll need to decide if it's "right". Let me know.

 

[spydey]

First, if you look at your original formula, and write out the ratio algebraically, you will see that the C3 value is irrelevant: it cancels out. You can remove it in your formula, or replace it with a 1, and get this version:

=IF(OR(F3=0,AND(H3:L3="N/A")),"N/A",SUM(IF(H3:L3<>"N/A",H3:L3,0)*{0.4,0.05,0.25,0.1,0.2})/(1-SUM(IF(H3:L3="N/A",1,0)*{0.4,0.05,0.25,0.1,0.2})))
with CSE.

Given that, your combined version might look something like this:

=(SUM(IF(H3:L6="N/A",0,H3:L6)*{0.4,0.05,0.25,0.1,0.2}))/(4-SUM(IF(H3:L6="N/A",1,0)*{0.4,0.05,0.25,0.1,0.2}))
with CSE.

I get an answer, but you'll need to decide if it's "right". Let me know.

Eric,

Thanks for the insight. I will take a look at it and see what I come up with.

-Spydey

P.S.

Here are the values I currently have:

C3=30
H3=1.00
I3=N/A
J3=0.00
K3=N/A
L3=.3333

 

[spydey]

Just to give a little background regarding the C3 value in my original formula, I think it plays a part because of how I am using it.

C column represents the maximum number of points attainable. C3:C6 all have different maximum points assigned. Columns H:L allot "points" as a percentage of the Maximum Points attainable as found in column C.

The current H:L allotment/percentages are:

H=.40
I=.05
J=.25
K=.10
L=.20

Having the C column (e.g. C3) in my formula allows me to exclude the columns (H:L) where N/A is present, and thus the total possible number of points needs to be adjusted accordingly.

Example: If I have Items H:L, and each item is worth 20 points, then there is a maximum of 100 points. However, if H has an N/A whereas I:L have data, then there is only 80 total points possible. The ending ratio would be something like this: 63/80 = 78.75% (fictitious)

Having C3 in the denominator where it is, allows me to calculate the total number of maximum points (C3), subtract the allotted points for the N/A items, and determine the possible points for that row.

Maximum Points = Column C
Possible Points = H:L <> N/A

I hope that makes sense.

Here are my current values:

C3=30
H3=1.0
I3=N/A
J3=0.0
K3=N/A
L3=.3333

So plugging that in to my formula, taking out the If(Or(And statement in the beginning, gives me:

=SUM(30*((0.4*1),N/A,(0.25*0.0),N/A,(0.2*0.3333))/(30-SUM(30*(0.05,0.10)))

Simplifying:

=SUM(30*(0.4,0.0,0.06666))/(30-SUM(1.5,3.0))

Simplifying:

=SUM(12.0,0.0,1.9998)/(30-4.5)

Simplifying:

=13.9998/25.5

=.5490

= 54.90%

It works as expected when doing only the single rows.

But when I try to combine it all into one single formula, to cover an array of C3:C6 & H3:L6, the formula in the denominator section of the ratio is what I am fighting with. Specifically the "... /(C3:C6-SUM(...." part. I feel that is where my formula is going awry.

Phew!! That was kind of long winded!!

Maybe I am doing the math wrong .....

What do you think Eric ... and/or anyone else?

-Spydey

 

[Eric W]

You're constructing a percentage. 15/30 = 50% and 27/54 = 50%. It does not matter what the maximum value is, half of the maximum value will always be 50%.

Look at your formula:

=SUM(30*((0.4*1),N/A,(0.25*0.0),N/A,(0.2*0.3333))/(30-SUM(30*(0.05,0.10)))

This roughly is the same as:

30*F1/(30-30*F2)
or
30*F1/(30(1-F2))
or
F1/(1-F2)

If you still don't believe me, change the value in C3 and leave everything else alone. No matter what you change it to, the result of your formula will not change. You can put my formula in another cell, and no matter what values you use, the results of both formulas should be the same.

I'd also suggest taking 4 rows of data and hand calculating the result you want. 4 rows X 5 columns X used 2 times each = 40 values. Somewhat tedious, but doable. Then see if the formula I gave you gives the same result. If not, please show your data and expected result, and I'll take a look at it.

 

[spydey]

You're constructing a percentage. 15/30 = 50% and 27/54 = 50%. It does not matter what the maximum value is, half of the maximum value will always be 50%.

Look at your formula:

=SUM(30*((0.4*1),N/A,(0.25*0.0),N/A,(0.2*0.3333))/(30-SUM(30*(0.05,0.10)))

This roughly is the same as:

30*F1/(30-30*F2)
or
30*F1/(30(1-F2))
or
F1/(1-F2)

If you still don't believe me, change the value in C3 and leave everything else alone. No matter what you change it to, the result of your formula will not change. You can put my formula in another cell, and no matter what values you use, the results of both formulas should be the same.

I'd also suggest taking 4 rows of data and hand calculating the result you want. 4 rows X 5 columns X used 2 times each = 40 values. Somewhat tedious, but doable. Then see if the formula I gave you gives the same result. If not, please show your data and expected result, and I'll take a look at it.

Eric,

I just wanted to say thanks for taking the time to work on this with me. I very much appreciate it. If it doesn't seem like it at some point, I apologize as it is probably just the text not containing inflection and facial expressions ....... but I guess that is why we have emoticons, right!! :D :D

Yes, you are correct with what you have stated. I completely forgot that I wouldn't need to represent it with the actual # of points (30, 15, etc etc) as it is a percentage of 1.

I will give what you suggested a try and let you know my results.

Take care and Happy Friday!!

-Spydey

 

[spydey]

First, if you look at your original formula, and write out the ratio algebraically, you will see that the C3 value is irrelevant: it cancels out. You can remove it in your formula, or replace it with a 1, and get this version:

=IF(OR(F3=0,AND(H3:L3="N/A")),"N/A",SUM(IF(H3:L3<>"N/A",H3:L3,0)*{0.4,0.05,0.25,0.1,0.2})/(1-SUM(IF(H3:L3="N/A",1,0)*{0.4,0.05,0.25,0.1,0.2})))
with CSE.

Given that, your combined version might look something like this:

=(SUM(IF(H3:L6="N/A",0,H3:L6)*{0.4,0.05,0.25,0.1,0.2}))/(4-SUM(IF(H3:L6="N/A",1,0)*{0.4,0.05,0.25,0.1,0.2}))
with CSE.

I get an answer, but you'll need to decide if it's "right". Let me know.

Eric,

After manually calculating it all out on paper, and then using the adjusted formula you provided (thank you for that), it comes to the same thing.

Thank you so much! You took the time to explain a few things to me, jog my memory, and provided a clear and concise response. I very much appreciate it!

Thank you again.

-Spydey

 

[Eric W]

Glad I could help!