SerinaStar
asked on
Formula Error #Value!
Hi,
I have been lumped with a spreadsheet to up keep and I am having trouble with a few of the formulas.
The error is in workbook 'Daily Schedule'
In cells AF4 - AL4, the formula is working however when I click into it the #Value! appears in the cell. This is the formula below:
=SUM(('Daily Schedule'!$F$7:$F$26=AF1)* ('Daily Schedule'!$G$7:$G$26="FAUL T")*('Dail y Schedule'!$K$7:$K$26<>0))
Now it is also only looking down to row 26 and when I try and change it to 1000 it shows the Error #Value! again.
The same problem is happening with the formula in the next row as well. This is the formula below:
=SUM(('Daily Schedule'!$F$7:$F$26=AF1)* ('Daily Schedule'!$G$7:$G$26="FAUL T")*'Daily Schedule'!$AA$7:$AA$26)
I am really not sure what I am doing wrong. I have attached the spreadsheet for you to have a look at.
Daily-Schedule-Orginal.xls
I have been lumped with a spreadsheet to up keep and I am having trouble with a few of the formulas.
The error is in workbook 'Daily Schedule'
In cells AF4 - AL4, the formula is working however when I click into it the #Value! appears in the cell. This is the formula below:
=SUM(('Daily Schedule'!$F$7:$F$26=AF1)*
Now it is also only looking down to row 26 and when I try and change it to 1000 it shows the Error #Value! again.
The same problem is happening with the formula in the next row as well. This is the formula below:
=SUM(('Daily Schedule'!$F$7:$F$26=AF1)*
I am really not sure what I am doing wrong. I have attached the spreadsheet for you to have a look at.
Daily-Schedule-Orginal.xls
Hello SerinaStar,
the formula is an Array formula and needs to be confirmed with Ctrl-Shift-Enter. Select a cell with the error, hit F2 to edit the formula and then Ctrl-Shift-Enter without changing anything.
cheers, teylyn
the formula is an Array formula and needs to be confirmed with Ctrl-Shift-Enter. Select a cell with the error, hit F2 to edit the formula and then Ctrl-Shift-Enter without changing anything.
cheers, teylyn
ASKER
Oh ok I see now.
Is this the right way to calculate this cells or is there a better option. I was hoping that it would just automatically do it.
Is this the right way to calculate this cells or is there a better option. I was hoping that it would just automatically do it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent I just to tried that and it works well. Thankyou so much for you quick response!!
ASKER
Works excellently....Thankyou so much for you quick response.
press F2
press Ctrl-Alt-Enter