Conditional formatting

[squic657]

Good morning!

I'm working on a very primitive version of a bed board. I want Excel to do the following:

If any cell in B7:F7(b through f represent the days of the week)= 0 (zero means no available beds),
then default all cells to 0.

Otherwise, sum discharges+transfers+intakes for each day(ex. b4:b6, c4:c6, etc.)

Here's what I have so far (I suck at this so bear with me):

=(IF(OR(B7:F7=0),0,SUM(B4:B6)))

Thanks and have a great day!

Cara

[:) Sixthsense :)]

Hi squic657,

Welcome to the forum

Not seem to be a conditional formatting question

Please attach a sample workbook with dummy data by covering the expected output for better understanding

Refer my signature area to know about how to attach file.

[dilipandey]

HI Cara,

welcome to the forum.
Can you upload a sample workbook.. ? thx



Regards,
DILIPandey
<click on below * if this helps>

[squic657]

Admissions Excel Formulas.xlsx

Good morning folks,

Here's the attachment for the question that's troubling me. The only reason I believe this question relates to conditional formatting is because of the "if, then" logic involved:

IF x₅=0, THEN make all PREVIOUS x's in the series 0.

DILIPandey and Sixthsense: I hope this helps you better understand the question I have. Thanks for such a prompt response yesterday morning! You gentlemen are the Best!

Cara

[Hypex]

Hey

Hopefully I've understood what you're looking for, but try

=IF(B7=0,0,SUM(B3+B5+B6))

in B8 and drag across to fill the other cells.

[dilipandey]

Hi Cara,

conditional formatting can not make any cell zero. It is used just for formatting (bold / italic / color etc).
also for

IF x5=0, THEN make all PREVIOUS x's in the series 0.

, X5 here itself is sum of previous Xs... so not sure what you need.


Regards,
DILIPandey
<click on below * if this helps>

[squic657]

Thanks Hypex! You understood what I was looking to do. That's where I got stuck.

Cara

[squic657]

DILIPandey, it's cool. I don't think what I want can be done in Excel. Thanks again!

[dilipandey]

Hi Cara, please mark this thread as [SOLVED], if your query stands resolved.. thx



Regards,
DILIPandey
<click on below * if this helps>

[ThomasCarter]

It can't be done with formula without creating a circular reference (infinite loop of checking if 0, if so make it 0, check again, make 0 again... etc)

It can be done using VBA Code though:

Please Login or Register  to view this content.

But I fear I've caused an infinite loop here too and not sure how to end it after the initial check... Can someone better with VBA than me please fix it? Hehe.

(Note you can use this one if you just hit the Escape key after a few seconds, so the code ends after only doing a few loops, everything will be 0. But I'm sure it's an easy fix to remove the infinite loop.)

[squic657]

Will do! Thanks again for your help!

[squic657]

Thanks ThomasCarter! I can't wait to try it.

Cara