Formula Help

[lex1020]

So I am building a spread sheet to track attendance among a certain number of days for a long period of time. The tracker divides attendance by weeks and puts out a number each week for the number of times you attended, that's simple. What I currently have it do is add up the number, and if it is more than 2 make it 2, if it is less than 2 make it what it is, and if there is no record for that week make it blank. Formula:=IF((SUM(B21:D24)>2),2, If(Sum(B21:D24)=0, "", SUM(B21:D24))), All this information is spit out into Cell B22.

What I want it to do is if cell B22 is blank, but cell E22 has a number in it to make B22 a 0, but if B22 already has a number to leave that number in B22. Can someone figure out a formula to do that? I can't seem to get it. I hope this makes sense.

 

[Michael M]

Try

=IF(AND(B22="",E22>0),0,IF(SUM(B21:D24)>2,2,IF(SUM(B21:D24)=0,"",SUM(B21:D24))))

 

[lex1020]

Try

=IF(AND(B22="",E22>0),0,IF(SUM(B21:D24)>2,2,IF(SUM(B21:D24)=0,"",SUM(B21:D24))))

Worked perfectly. Thank you very much.

 

[lex1020]

Try

=IF(AND(B22="",E22>0),0,IF(SUM(B21:D24)>2,2,IF(SUM(B21:D24)=0,"",SUM(B21:D24))))

Actually sorry. I was wrong. It comes up with a reference error saying circular dependency detected. I made a slight mistake in my earlier post. It was supposed to be Cells B25 and E25 instead of B22 and E22

 

[Michael M]

So, did you change the cell references in the formula from 22 to 25 ?....is it working now ?

 

[lex1020]

It works when the cell reference is 22, but when I set it to 25 it becomes a self referencing formula. Sorry.

 

[lex1020]

So I seem to have gotten it to work, but its a little more complicated. What I did was,

=IF(AND(B22="",B21="",B23="",B24="",C22="",D22="",C21="",C23="",C24="",D23="",D24="",SUM(E21:G24)>0),0,IF(SUM(B21:D24)>2,2,IF(SUM(B21:D24)=0,"",SUM(B21:D24))))

So that instead of it referencing itself it references the components that make up itself. It seems to work. Can you spot any problems with the code that might prevent it from doing what I want please?

 

[Michael M]

=IF(AND(B21:D24="",SUM(E21:G24)>0),0,IF(SUM(B21:D24)>2,2,IF(SUM(B21:D24)=0,"",SUM(B21:D24))))

Entered with CTRL + SHIFT + Enter

 

[lex1020]

=IF(AND(B21:D24="",SUM(E21:G24)>0),0,IF(SUM(B21:D24)>2,2,IF(SUM(B21:D24)=0,"",SUM(B21:D24))))

Entered with CTRL + SHIFT + Enter

I tried that, but its a merged cell and so it comes up as array formulas are not valid in merged cells, and when I enter it normally it comes up #VALUE! error. But doing it the way I wrote seems to work fine. Thank you for your help in giving me a formula to start with.

 

[Michael M]

OK......but why are you using merged cells.
They are the worst thing ever created by Microsoft, and will cause you a tone of heartache, if you continue to use them.
Try changing instead to.....
high;ight the merged cells, Unmerge them, then
Format / cells / Alignment tab / horizontal / "Center Across Selection"