long formula

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 insertname 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


--

Regards,

Peo Sjoblom

wrote in message
...
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)

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]

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Peo Sjoblom" wrote in message
...
Please stay in the thread..

Is there a reason for using MAXA/MINA?



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.598 / Virus Database: 380 - Release Date: 28/02/2004

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


-----Original Message-----
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 insertname 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


--

Regards,

Peo Sjoblom

wrote in message
...
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)


.

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.

--

Regards,

Peo Sjoblom

wrote in message
...
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


-----Original Message-----
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 insertname 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


--

Regards,

Peo Sjoblom

wrote in message
...
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)


.

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



wrote in message
...
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


-----Original Message-----
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 insertname 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


--

Regards,

Peo Sjoblom

wrote in message
...
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)


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.598 / Virus Database: 380 - Release Date: 28/02/2004

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(MYBIGCALC90%, MYBIGCALC=10%)...


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

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


In article ,
wrote:

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?

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(MYBIGCALC90%, MYBIGCALC=10%)...


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

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


In article ,
wrote:

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?
.

The example is not clear. What if the month is not greater than 10 and/or
the day is not greater than 21 and/or the year is not greater than 1994? Are
you trying to say that the formula doesn't apply if the date in A1:A3 is
10/21/1994 or earlier? If the years of service are exactly 3 , is it 10 days
accrued or 15 days accrued? Do you mean between 10 (not 20) years and 15
years in your last criterion?

To get an accurate answer you need to take the trouble to define the problem
more precisely and accurately so people don't have to guess.

--

Vasant

"Jennifer" wrote in message
...
I need a formula to calculate days accrued based on years of service. For
example:

cells A1=month, a2=day and a3=year (of date of hire)
the conditions are as follows:
if the month is greater that 10(october), the day is greater that 21, and
the year is greater than 1994 and you have the following years of service
(calculated from cell a3-year and TODAY):
between 1 & 60 calander days (from the year in cell a3) = 0 day accrued
between 60 calander days & 1 year (from the year in cell a3) = 7 days
accrued
between 1 year & 3 years (from the year in cell a3) = 10 days accrued
between 3 year & 5 years (from the year in cell a3) = 15 days accrued
between 5 year & 10 years (from the year in cell a3) = 20 days accrued
between 20 year & 15 years (from the year in cell a3) = 25 days accrued
more than 15 years (from the year in cell a3) = 30 days accrued

Can someone help ?! Thank you...

Thanks, but what is VB code ? (I'm learning as I go, forgive me)