long formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Could you suggest a way to trim this formula down?

IF(OR((((MAXA(B3:U3)-U5)/(MAXA(B3:U3)-MINA(B4:U4))))>90%,
(((MAXA(B3:U3)-U5)/(MAXA(B3:U3)-MINA(B4:U4))))<=10%),
((((SUM(S5:U5)/3)*6)+((SUM(R5:U5)/4)+(SUM(Q5:U5)/5)+(SUM
(M5:U5)/9)+(SUM(L5:U5)/10)+(SUM(B5:U5)/20)))/11)
 
Please stay in the thread..

Is there a reason for using MAXA/MINA, also you have far too many
parenthesis..

You could for instance name parts of the formula like

=OR(MAXA($B$12:$U$12)-$U$14)/(MAXA($B$12:$U$12)-MINA($B$13:$U$13))>90%,(MAXA
($B$12:$U$12)-$U$14)/(MAXA($B$12:$U$12)-MINA($B$13:$U$13))<=10%)

do insert>name define, let's call it Part1

then you can use

=IF(Part1,(((SUM($S$14:$U$14)/3)*6+(SUM($R$14:$U$14)/4+(SUM($Q$6:$U$14)/5)+(
SUM($M$6:$U$14)/9)+(SUM($L$6:$U$14)/10)+(SUM($B$14:$U$14)/20)))/11))

do the same with the Sum part, call it Part2
then use

=IF(Part1,Part2)




--

Regards,

Peo Sjoblom
 
Is there a reason for using MAXA/MINA?

[jumps in]

To the OP - Gonna jump in for 2 secs just to clarify on this one. What Peo
means is that the normal functions used are MAX and MIN as opposed to MAXA and
MINA. The normal ones exclude text and logical values whereas the ones you have
used specifically include them, so unless you are really using them for that
reason, then you are taking up chunks of space in your formula for no reason.

[jumps out again]
 
I want to have the entire formula in one cell. doing a
define/name will have part of the formula in a different
cell. Any other suggestions?

I could change MAXA to MAX and MINA to MIN
 
No, it won't. When you define the name put the formula with absolute
references (otherwise it will change depending on where you have the
main formula), DO NOT put it in a cell and refer to the cell. Use it the
way I showed in one of your multiple posts with an equal sign
followed by the formula with absolute ($A$1) references, give it a name and
use it.
 
No it won't. A named formula exists only in Excel's memory and makes no use of
any other cells whatsoever.

Insert / Name / Define

Put formula in where you see a cell reference, REPLACING whatever the cell
reference is, and then just give it a name.
 
You don't need to have part of the formula in a different cell

Insert/Name/Define

Name in workbook: MYMAX
Refers To: =MAXA($B$12:$U$12)


Name in workbook: MYMIN
Refers To: =MINA($B$13:$U$13)

so your formula becomes

=OR((MYMAX-$U$14)/(MYMAX-MYMIN))>90%...


You can even make the references relative by leaving out the $ in the
row number. Select a cell in row 12. Then you can enter

Name in workbook: MYBIGCALC
Refers To: =(MAXA($B12:$U12)-$U14)/(MAXA($B12:$U12)-MINA($B13:$U13))

and your formula reduces to

A12: =OR(MYBIGCALC>90%, MYBIGCALC<=10%)...


If now you enter MYBIGCALC in A20, then MYBIGCALC would evaluate to

=(MAXA($B20:$U20)-$U22)/(MAXA($B20:$U20)-MINA($B21:$U21))
 
THANK YOU GUYS
-----Original Message-----
You don't need to have part of the formula in a different cell

Insert/Name/Define

Name in workbook: MYMAX
Refers To: =MAXA($B$12:$U$12)


Name in workbook: MYMIN
Refers To: =MINA($B$13:$U$13)

so your formula becomes

=OR((MYMAX-$U$14)/(MYMAX-MYMIN))>90%...


You can even make the references relative by leaving out the $ in the
row number. Select a cell in row 12. Then you can enter

Name in workbook: MYBIGCALC
Refers To: =(MAXA($B12:$U12)-$U14)/(MAXA($B12:$U12)-MINA ($B13:$U13))

and your formula reduces to

A12: =OR(MYBIGCALC>90%, MYBIGCALC<=10%)...


If now you enter MYBIGCALC in A20, then MYBIGCALC would evaluate to

=(MAXA($B20:$U20)-$U22)/(MAXA($B20:$U20)-MINA ($B21:$U21))



.
Click to expand...
 
Back
Top