# Formula Help

#### lex1020

##### New Member
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.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try
Code:
``=IF(AND(B22="",E22>0),0,IF(SUM(B21:D24)>2,2,IF(SUM(B21:D24)=0,"",SUM(B21:D24))))``

Try
Code:
``=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.

Try
Code:
``=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

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

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

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

Code:
``=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?

Code:
``=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

Code:
``=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.

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.
high;ight the merged cells, Unmerge them, then
Format / cells / Alignment tab / horizontal / "Center Across Selection"

Replies
12
Views
219
Replies
4
Views
139
Replies
5
Views
290
Replies
2
Views
388
Replies
2
Views
378

### Forum statistics

1,203,509
Messages
6,055,818
Members
444,828
Latest member
StaffordStag ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back