Formula Too Long!

[artiststevens]

Hello,
Can anyone help me too shorten this formula? It's too long for excel. It's on the attached Word doc. Thank you.

[martindwilson]

are you sure the () are in the right places?
shouldnt
ABS(AVERAGE(B14:D27)-
be
ABS(AVERAGE(B14:D27))-
and so on?

[artiststevens]

Thank you for the help. When I enter the additional ")" the answer to the equation changes but I'm making progress. Any further help would be greatly appreciated.

[artiststevens]

I attached what I have so far. I'm trying to combine the calculations in columns "E:H" into one formula and place it in column "H", thus eliminating the need for columns E:H. Column "I" is what I have so far. Any help would be greatly appreciated.

[JBeaucaire]

Without even analyzing the formulas, if H24 references E24, F24 and G24, you can simply insert the formulas FROM those cells into cell H24 in the spots where the cell references are. This creates a megaformula that only you will probably be able to make sense of, but it is your formulas still operating.

=(E24-F24)/(0.015*G24)

...becomes

=(((B24+C24+D24)/3)-AVERAGE(E11:E24))/(0.015*((ABS(F24-E24)+ABS(F24-E23)+ABS(F24-E22)+ABS(F24-E21)+ABS(F24-E20)+ABS(F24-E19)+ABS(F24-E18)+ABS(F24-E17)+ABS(F24-E16)+ABS(F24-E15)+ABS(F24-E14)+ABS(F24-E13)+ABS(F24-E12)+ABS(F24-E11))/14))

[JBeaucaire]

This array formula replaces your long version and accomplishes the same math. In H24:

=(((B24+C24+D24)/3)-AVERAGE(E11:E24))/(0.015*(SUM(ABS(F24-(E11:E24)))/14))

...confirmed with CTRL-SHIFT-ENTER to activate the array. The first value should appear properly, then you can copy that cell downward.

[artiststevens]

Thank you very much Jerry! That did the trick I really appreciate you taking the time to help me solve this problem.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.