formula"IF (cell a) is not "A" or 'B", then see IF (cel b) is blank and thenreturn 0 or 10

Hi ,

I'm struggling creating the right formula.

I have a table that contains a capacity overview (A) of people in the team.
in column B, I have the names of people
in column C, D, E etc I have planned projects per week

I have a shadow tabel that looks at planned projects and let's me know what my actual capacity(B) is per week( 10 per person) with the following formula:
=IF(ISBLANK(b2);"0";"10")

which works fine.

However, sometimes, column B contains two people that I do want to see in my overview(A), but should not be counted in my capacity(B), and therefore do nto count as '10', but as "0",

I've tried something like this:
=if(isblank(b32);if(or(C32<>”Tim”,b32<>”Ronald”);"0";"10"))

I know the above is wrong as I have not stated the part where 'ronald and tim count as "0", but where to insert it??



can someone enlighten me please?

Hi,

Are you able to upload the workbook and manually add some examples of results you expect to see. These things are often easier and quicker to figure out if we can see the request in context.

Just looking at the formula, the FALSE clause for the first if is not present

=IF(ISBLANK(B32),IF(OR(C32<>”Tim”,B32<>”Ronald”),0,10),WhatHappensWhenB32IsNotBlank?)

Blue would be the test, Green is what happens if TRUE, red happens if FALSE.

The reason you are getting errors with this syntax, is the second (green) if will only happen if B32 is blank, or the Blue IF test returns TRUE. I think you meant to have the GREEN test occur only if B32 is NOT blank?

Try changing the structure to

=IF(B32="","",IF(OR(NOT(C32="Tim"),NOT(B32="Ronald")),0,10))

1. stating that if cell b32 is not 'ronald'or 'kwaliteit it should ignore statement 2 and no nothing
2. stating that if cell c32 is blank, put 0 in it, if isn't, count as 10.


Like this?

=IF(NOT(OR(B32="ronald",B32="frank",B32="somethingelse")),"",IF(C32="",0,"10"))

I'll break your formula down a bit so you can see exactly what's happening in each part, and maybe figure out the solution. I'm a little confused on the end result expected
Your formula;
=IF(NOT(OR(B32="ronald";B32="kwaliteit"));"";IF(C32="";10;"0"))

Breakdown;
=IF(
(B32="ronald";B32="kwaliteit") <------ will return TRUE or FALSE, if either condition is met, B32 = "ronald" will be TRUE if B32 is ronald

The NOT() around the OR statement NOT(OR(....)) will invert it, so TRUE becomes FALSE and FALSE becomes TRUE. This effectively says "return TRUE in all cases EXCEPT if B32 is 'ronald' or 'kwaliteit'

If the previous test results in a TRUE, then "" (leave the cell Blank)

If the test results in a FALSE, then start another test on cell C32.

IF(C32="";10;"0")
If C32 is completely blank, return 10, otherwise return 0

Originally Posted by pallie

Richard, I have it attached now , have a look at red cell AF 32

Hi,

Maybe

Please Login or Register  to view this content.