Formula too long
 

Good afternoon

Does anyone have any ideas on shortening the formula below - its apparently
too long (its looking thru a range of cells on different sheets for a
specific value).

=IF('Assessment 1'!M12*'Assessment 2'!M12*'Assessment 3'!M12*'Assessment
1'!M14*'Assessment 2'!M14*'Assessment 3'!M14*'Assessment 1'!M19*'Assessment
2'!M19*'Assessment 3'!M19*'Assessment 1'!M21*'Assessment 2'!M21*'Assessment
3'!M21*'Assessment 1'!M23*'Assessment 2'!M23*'Assessment 3'!M23*'Assessment
1'!M28*'Assessment 2'!M28*'Assessment 3'!M28*'Assessment 1'!M30*'Assessment
2'!M30*'Assessment 3'!M30*'Assessment 1'!M32*'Assessment 2'!M32*'Assessment
3'!M32*'Assessment 1'!M37*'Assessment 2'!M37*'Assessment 3'!M37*'Assessment
1'!M39*'Assessment 2'!M39*'Assessment 3'!M39*'Assessment 1'!M41*'Assessment
2'!M41*'Assessment 3'!M41*'Assessment 1'!M46*'Assessment 2'!M46*'Assessment
3'!M46*'Assessment 1'!M48*'Assessment 2'!M48*'Assessment 3'!M48*'Assessment
1'!M50*'Assessment 2'!M50*'Assessment 3'!M50*'Assessment 1'!M55*'Assessment
2'!M55*'Assessment 3'!M55*'Assessment 1'!M57*'Assessment 2'!M57*'Assessment
3'!M57*'Assessment 1'!M59*'Assessment 2'!M59*'Assessment 3'!M59*'Assessment
1'!M64*'Assessment 2'!M64*'Assessment 3'!M64*'Assessment 1'!M66*'Assessment
2'!M66*'Assessment 3'!M66*'Assessment 1'!M68*'Assessment 2'!M68*'Assessment
3'!M68=0,"Fail","Pass")

Thanks

Formula too long
 

Am I reading this wrong or are you multiply all the cells together and if it
= 0, "Fail"?
I don't think it is searching for a specific value, but I could be wrong.

Les

"JaB" wrote:

Good afternoon

Does anyone have any ideas on shortening the formula below - its apparently
too long (its looking thru a range of cells on different sheets for a
specific value).

=IF('Assessment 1'!M12*'Assessment 2'!M12*'Assessment 3'!M12*'Assessment
1'!M14*'Assessment 2'!M14*'Assessment 3'!M14*'Assessment 1'!M19*'Assessment
2'!M19*'Assessment 3'!M19*'Assessment 1'!M21*'Assessment 2'!M21*'Assessment
3'!M21*'Assessment 1'!M23*'Assessment 2'!M23*'Assessment 3'!M23*'Assessment
1'!M28*'Assessment 2'!M28*'Assessment 3'!M28*'Assessment 1'!M30*'Assessment
2'!M30*'Assessment 3'!M30*'Assessment 1'!M32*'Assessment 2'!M32*'Assessment
3'!M32*'Assessment 1'!M37*'Assessment 2'!M37*'Assessment 3'!M37*'Assessment
1'!M39*'Assessment 2'!M39*'Assessment 3'!M39*'Assessment 1'!M41*'Assessment
2'!M41*'Assessment 3'!M41*'Assessment 1'!M46*'Assessment 2'!M46*'Assessment
3'!M46*'Assessment 1'!M48*'Assessment 2'!M48*'Assessment 3'!M48*'Assessment
1'!M50*'Assessment 2'!M50*'Assessment 3'!M50*'Assessment 1'!M55*'Assessment
2'!M55*'Assessment 3'!M55*'Assessment 1'!M57*'Assessment 2'!M57*'Assessment
3'!M57*'Assessment 1'!M59*'Assessment 2'!M59*'Assessment 3'!M59*'Assessment
1'!M64*'Assessment 2'!M64*'Assessment 3'!M64*'Assessment 1'!M66*'Assessment
2'!M66*'Assessment 3'!M66*'Assessment 1'!M68*'Assessment 2'!M68*'Assessment
3'!M68=0,"Fail","Pass")

Thanks

Formula too long
 

Hopefully (!) its looking through those cells to see if any of them have a
zero in it. If it finds one I want it to return the message 'FAIL', if not,
'PASS'.

"WLMPilot" wrote:

Am I reading this wrong or are you multiply all the cells together and if it
= 0, "Fail"?
I don't think it is searching for a specific value, but I could be wrong.

Les

"JaB" wrote:

Good afternoon

Does anyone have any ideas on shortening the formula below - its apparently
too long (its looking thru a range of cells on different sheets for a
specific value).

=IF('Assessment 1'!M12*'Assessment 2'!M12*'Assessment 3'!M12*'Assessment
1'!M14*'Assessment 2'!M14*'Assessment 3'!M14*'Assessment 1'!M19*'Assessment
2'!M19*'Assessment 3'!M19*'Assessment 1'!M21*'Assessment 2'!M21*'Assessment
3'!M21*'Assessment 1'!M23*'Assessment 2'!M23*'Assessment 3'!M23*'Assessment
1'!M28*'Assessment 2'!M28*'Assessment 3'!M28*'Assessment 1'!M30*'Assessment
2'!M30*'Assessment 3'!M30*'Assessment 1'!M32*'Assessment 2'!M32*'Assessment
3'!M32*'Assessment 1'!M37*'Assessment 2'!M37*'Assessment 3'!M37*'Assessment
1'!M39*'Assessment 2'!M39*'Assessment 3'!M39*'Assessment 1'!M41*'Assessment
2'!M41*'Assessment 3'!M41*'Assessment 1'!M46*'Assessment 2'!M46*'Assessment
3'!M46*'Assessment 1'!M48*'Assessment 2'!M48*'Assessment 3'!M48*'Assessment
1'!M50*'Assessment 2'!M50*'Assessment 3'!M50*'Assessment 1'!M55*'Assessment
2'!M55*'Assessment 3'!M55*'Assessment 1'!M57*'Assessment 2'!M57*'Assessment
3'!M57*'Assessment 1'!M59*'Assessment 2'!M59*'Assessment 3'!M59*'Assessment
1'!M64*'Assessment 2'!M64*'Assessment 3'!M64*'Assessment 1'!M66*'Assessment
2'!M66*'Assessment 3'!M66*'Assessment 1'!M68*'Assessment 2'!M68*'Assessment
3'!M68=0,"Fail","Pass")

Thanks

Formula too long
 

"JaB" wrote in message
...
Good afternoon

Does anyone have any ideas on shortening the formula below - its
apparently
too long (its looking thru a range of cells on different sheets for a
specific value).

=IF('Assessment 1'!M12*'Assessment 2'!M12*'Assessment 3'!M12*'Assessment
1'!M14*'Assessment 2'!M14*'Assessment 3'!M14*'Assessment
1'!M19*'Assessment
2'!M19*'Assessment 3'!M19*'Assessment 1'!M21*'Assessment
2'!M21*'Assessment
3'!M21*'Assessment 1'!M23*'Assessment 2'!M23*'Assessment
3'!M23*'Assessment
1'!M28*'Assessment 2'!M28*'Assessment 3'!M28*'Assessment
1'!M30*'Assessment
2'!M30*'Assessment 3'!M30*'Assessment 1'!M32*'Assessment
2'!M32*'Assessment
3'!M32*'Assessment 1'!M37*'Assessment 2'!M37*'Assessment
3'!M37*'Assessment
1'!M39*'Assessment 2'!M39*'Assessment 3'!M39*'Assessment
1'!M41*'Assessment
2'!M41*'Assessment 3'!M41*'Assessment 1'!M46*'Assessment
2'!M46*'Assessment
3'!M46*'Assessment 1'!M48*'Assessment 2'!M48*'Assessment
3'!M48*'Assessment
1'!M50*'Assessment 2'!M50*'Assessment 3'!M50*'Assessment
1'!M55*'Assessment
2'!M55*'Assessment 3'!M55*'Assessment 1'!M57*'Assessment
2'!M57*'Assessment
3'!M57*'Assessment 1'!M59*'Assessment 2'!M59*'Assessment
3'!M59*'Assessment
1'!M64*'Assessment 2'!M64*'Assessment 3'!M64*'Assessment
1'!M66*'Assessment
2'!M66*'Assessment 3'!M66*'Assessment 1'!M68*'Assessment
2'!M68*'Assessment
3'!M68=0,"Fail","Pass")

Thanks

There are 1160 characters in that formula. I believe the maximum is 256.
In that case could split the formula into 5 parts and then have a sixth
column to analyse the output and give your final answer? You could also
shorten the name of each sheet.

Regards.

Bill Ridgeway
Computer Solutions

Formula too long
 

Why not count the 0 values on each individual sheet and then run a formula
which sums those counts. If the count is 0, i.e., there is at least on 0
value, then "FAIL", else "PASS"

Dave
--
Brevity is the soul of wit.


"JaB" wrote:

Hopefully (!) its looking through those cells to see if any of them have a
zero in it. If it finds one I want it to return the message 'FAIL', if not,
'PASS'.

"WLMPilot" wrote:

Am I reading this wrong or are you multiply all the cells together and if it
= 0, "Fail"?
I don't think it is searching for a specific value, but I could be wrong.

Les

"JaB" wrote:

Good afternoon

Does anyone have any ideas on shortening the formula below - its apparently
too long (its looking thru a range of cells on different sheets for a
specific value).

=IF('Assessment 1'!M12*'Assessment 2'!M12*'Assessment 3'!M12*'Assessment
1'!M14*'Assessment 2'!M14*'Assessment 3'!M14*'Assessment 1'!M19*'Assessment
2'!M19*'Assessment 3'!M19*'Assessment 1'!M21*'Assessment 2'!M21*'Assessment
3'!M21*'Assessment 1'!M23*'Assessment 2'!M23*'Assessment 3'!M23*'Assessment
1'!M28*'Assessment 2'!M28*'Assessment 3'!M28*'Assessment 1'!M30*'Assessment
2'!M30*'Assessment 3'!M30*'Assessment 1'!M32*'Assessment 2'!M32*'Assessment
3'!M32*'Assessment 1'!M37*'Assessment 2'!M37*'Assessment 3'!M37*'Assessment
1'!M39*'Assessment 2'!M39*'Assessment 3'!M39*'Assessment 1'!M41*'Assessment
2'!M41*'Assessment 3'!M41*'Assessment 1'!M46*'Assessment 2'!M46*'Assessment
3'!M46*'Assessment 1'!M48*'Assessment 2'!M48*'Assessment 3'!M48*'Assessment
1'!M50*'Assessment 2'!M50*'Assessment 3'!M50*'Assessment 1'!M55*'Assessment
2'!M55*'Assessment 3'!M55*'Assessment 1'!M57*'Assessment 2'!M57*'Assessment
3'!M57*'Assessment 1'!M59*'Assessment 2'!M59*'Assessment 3'!M59*'Assessment
1'!M64*'Assessment 2'!M64*'Assessment 3'!M64*'Assessment 1'!M66*'Assessment
2'!M66*'Assessment 3'!M66*'Assessment 1'!M68*'Assessment 2'!M68*'Assessment
3'!M68=0,"Fail","Pass")

Thanks

Formula too long
 

Try this: One each worksheet (Assessment 1, Assessment 2, Assement 3) enter
a formula in a column or row that us can hide that will multiply each of the
cells on that worksheet - then where you want the fail / pass result enter
the if statement multipling the 3 results from above.

"JaB" wrote:

Good afternoon

Does anyone have any ideas on shortening the formula below - its apparently
too long (its looking thru a range of cells on different sheets for a
specific value).

=IF('Assessment 1'!M12*'Assessment 2'!M12*'Assessment 3'!M12*'Assessment
1'!M14*'Assessment 2'!M14*'Assessment 3'!M14*'Assessment 1'!M19*'Assessment
2'!M19*'Assessment 3'!M19*'Assessment 1'!M21*'Assessment 2'!M21*'Assessment
3'!M21*'Assessment 1'!M23*'Assessment 2'!M23*'Assessment 3'!M23*'Assessment
1'!M28*'Assessment 2'!M28*'Assessment 3'!M28*'Assessment 1'!M30*'Assessment
2'!M30*'Assessment 3'!M30*'Assessment 1'!M32*'Assessment 2'!M32*'Assessment
3'!M32*'Assessment 1'!M37*'Assessment 2'!M37*'Assessment 3'!M37*'Assessment
1'!M39*'Assessment 2'!M39*'Assessment 3'!M39*'Assessment 1'!M41*'Assessment
2'!M41*'Assessment 3'!M41*'Assessment 1'!M46*'Assessment 2'!M46*'Assessment
3'!M46*'Assessment 1'!M48*'Assessment 2'!M48*'Assessment 3'!M48*'Assessment
1'!M50*'Assessment 2'!M50*'Assessment 3'!M50*'Assessment 1'!M55*'Assessment
2'!M55*'Assessment 3'!M55*'Assessment 1'!M57*'Assessment 2'!M57*'Assessment
3'!M57*'Assessment 1'!M59*'Assessment 2'!M59*'Assessment 3'!M59*'Assessment
1'!M64*'Assessment 2'!M64*'Assessment 3'!M64*'Assessment 1'!M66*'Assessment
2'!M66*'Assessment 3'!M66*'Assessment 1'!M68*'Assessment 2'!M68*'Assessment
3'!M68=0,"Fail","Pass")

Thanks

Formula too long
 

Then you need to do a LOOKUP of some sort to determine if there is a zero
value. Because of the number of worksheets involved and length of worksheet
name, I would perform this lookup per worksheet, then take that data and
determine your pass/fail on the worksheet that you need the data.

Les

"Dave F" wrote:

Why not count the 0 values on each individual sheet and then run a formula
which sums those counts. If the count is 0, i.e., there is at least on 0
value, then "FAIL", else "PASS"

Dave
--
Brevity is the soul of wit.


"JaB" wrote:

Hopefully (!) its looking through those cells to see if any of them have a
zero in it. If it finds one I want it to return the message 'FAIL', if not,
'PASS'.

"WLMPilot" wrote:

Am I reading this wrong or are you multiply all the cells together and if it
= 0, "Fail"?
I don't think it is searching for a specific value, but I could be wrong.

Les

"JaB" wrote:

Good afternoon

Does anyone have any ideas on shortening the formula below - its apparently
too long (its looking thru a range of cells on different sheets for a
specific value).

=IF('Assessment 1'!M12*'Assessment 2'!M12*'Assessment 3'!M12*'Assessment
1'!M14*'Assessment 2'!M14*'Assessment 3'!M14*'Assessment 1'!M19*'Assessment
2'!M19*'Assessment 3'!M19*'Assessment 1'!M21*'Assessment 2'!M21*'Assessment
3'!M21*'Assessment 1'!M23*'Assessment 2'!M23*'Assessment 3'!M23*'Assessment
1'!M28*'Assessment 2'!M28*'Assessment 3'!M28*'Assessment 1'!M30*'Assessment
2'!M30*'Assessment 3'!M30*'Assessment 1'!M32*'Assessment 2'!M32*'Assessment
3'!M32*'Assessment 1'!M37*'Assessment 2'!M37*'Assessment 3'!M37*'Assessment
1'!M39*'Assessment 2'!M39*'Assessment 3'!M39*'Assessment 1'!M41*'Assessment
2'!M41*'Assessment 3'!M41*'Assessment 1'!M46*'Assessment 2'!M46*'Assessment
3'!M46*'Assessment 1'!M48*'Assessment 2'!M48*'Assessment 3'!M48*'Assessment
1'!M50*'Assessment 2'!M50*'Assessment 3'!M50*'Assessment 1'!M55*'Assessment
2'!M55*'Assessment 3'!M55*'Assessment 1'!M57*'Assessment 2'!M57*'Assessment
3'!M57*'Assessment 1'!M59*'Assessment 2'!M59*'Assessment 3'!M59*'Assessment
1'!M64*'Assessment 2'!M64*'Assessment 3'!M64*'Assessment 1'!M66*'Assessment
2'!M66*'Assessment 3'!M66*'Assessment 1'!M68*'Assessment 2'!M68*'Assessment
3'!M68=0,"Fail","Pass")

Thanks