Expert required for new formula

[Scott82]

Hi there

I am using two formulas
=IF(AND(COUNTBLANK('S:\School\Residential\Flexible Workers\Term 6 2011\[Schedules Flexible worker Term 6 2011.xls]Schedules'!E112:E120)>8,'Availability IN'!D16="x"),"Available","")

and

=SUM(C5+C8+C11+C14+C17+C20+C23)

I need a formula that will return True if the first formula shows available and the second is equel to or above 1.

Many thanks

Scott

 

[yytsunamiyy]

Try:

=IF(AND(COUNTBLANK('S:\School\Residential\Flexible Workers\Term 6 2011\[Schedules Flexible worker Term 6 2011.xls]Schedules'!E112:E120)>8,'Availability IN'!D16="x",SUM(C5,C8,C11,C14,C17,C20,C23)=>1),,)

 

[Scott82]

Hey, thanks for such a quick reply. I'm afraid it's coming up with a value error??

 

[yytsunamiyy]

Hey, thanks for such a quick reply. I'm afraid it's coming up with a value error??

What part of the formula gives the error? To test go to formula evaluation and step through the formula.

 

[Scott82]

Hi there, I entered this as an array and it has worked however it is saying true when I know that not all of them are!

 

[Scott82]

This is what I have:
=IF(AND(COUNTBLANK('[Schedules Flexible worker Term 6 2011.xls]Schedules'!E4:E12)>8,'[Availability Flexible worker Term 6 2011.xls]Availability IN'!D4="x",'[Master rota Term 6 2011.xls]Shift Requirements'!$C$5+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$8+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$11+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$14+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$17+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$20+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$23+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$29:$C$30)>=1,TRUE,FALSE)

I'm not sure if the last bit is right as the total sum of Master rota Term 6 ....... is to be higher than or equel to 1 not every cell. I'm not sure which this formula is stating!

Thanks

Scott

 

[yytsunamiyy]

your formula is extremly hard to read this way. It would be better if you wrap it in code tags (click on advanced, highlight your formula and click on the # symbol).

To take the logic of your formula apart it checks the following conditions to return true:

- '[Schedules Flexible worker Term 6 2011.xls]Schedules'!E4:E12 contains more than 8 blank cells
- '[Availability Flexible worker Term 6 2011.xls]Availability IN'!D4 has value "x"
- the sum of values in cells '[Master rota Term 6 2011.xls]Shift Requirements'!
*$C$5
*$C$8
*$C$11
*$C$14
*$C$17
*$C$20
*$C$23
*$C$29
*$C$30
is equal to or larger than 1.

To add C29:C30 you need to wrap it with a SUM formula. (shown red)

Your formula is missing a closing parenthesis for the AND part of the formula. (shown green). If the logic above is correct your formula should be:

=IF(AND(COUNTBLANK('[Schedules Flexible worker Term 6  2011.xls]Schedules'!E4:E12)>8,'[Availability Flexible worker Term 6  2011.xls]Availability IN'!D4="x",'[Master rota Term 6 2011.xls]Shift  Requirements'!$C$5+'[Master rota Term 6 2011.xls]Shift  Requirements'!$C$8+'[Master rota Term 6 2011.xls]Shift  Requirements'!$C$11+'[Master rota Term 6 2011.xls]Shift  Requirements'!$C$14+'[Master rota Term 6 2011.xls]Shift  Requirements'!$C$17+'[Master rota Term 6 2011.xls]Shift  Requirements'!$C$20+'[Master rota Term 6 2011.xls]Shift  Requirements'!$C$23+[B][COLOR=Red]SUM([/COLOR][/B]'[Master rota Term 6 2011.xls]Shift  Requirements'!$C$29:$C$30[B][COLOR=Red])[/COLOR][/B]>=1[COLOR=SeaGreen][B])[/B][/COLOR],TRUE,FALSE)

 

[Scott82]

Your logic is bang on however I'm still getting an error!

 

[yytsunamiyy]

What is your formula now? Have you stepped through your formula in the formula evaluation? If yes, at which point does it error out?
I'm on a system without XL here at the mo, so I can't test.

 

[Scott82]

First stage, IF(AND(#VALUE>8,