[Solved] IF statement problem

[Lee]

The available space D1 is floor space.
If there is no measurable ventilation data the D5 or D6 or D7 and they say 0 then divide D1 by C8 (50%) answer into D10
If there is ventilation type and D5>0 or D6>0 or D7>0 then the lowest number answer in D10
I hope that makes sense, happy to clarify if required. Thanks in advance.
Lee

 

[shamimarita]

Hello Lee,

Here’s a one-cell solution for D10 that follows your rules:

Rule logic:

• If no measurable ventilation (D57 all = 0) ⇒ use reduction: D1 × C8.
• If any ventilation value is given ⇒ use the lowest positive among D57.

Formula (modern Excel / 365) – enter in D10:

=IF(COUNTIF(D57,">0")=0,
D1*C8,
MIN(IF(D57>0,D57))
)

• (Spills naturally; if you’re on an older Excel, confirm with Ctrl+Shift+Enter.)

Non-array alternative (works in all versions):

=IF(COUNTIF(D57,">0")=0,
D1*C8,
AGGREGATE(15,6,D57/(D57>0),1)
)

Notes:

• Put 50% (i.e., 0.5 formatted as %)** in C8.
• Example with your numbers: D1=50, C8=50%, D5=10, D6=0, D7=0 → result is 10.
• If D5=D6=D7=0 → result is 25 (50 × 50%).

If you want blanks instead of 0 when D1 is empty, wrap the whole thing in IF(D1="","", … ).

 

[Lee]

=IF(COUNTIF(D2527,">0")=0,C28*C8,AGGREGATE(15,6,D2527/(D2527>0),1)
Hi,
Thanks for the response. I have entered the formula as you sent above. The trouble is D2527 can be any number and they need to be totalled into D30, if D2527 are "0" only then do we divide D23 by 50% which is C28. I hope that makes sense. It also needs to total D2527 into the D30 cell.

 

[shamimarita]

Hello Lee,

Thanks, that makes sense. If D2527 should be added up (any numbers), and only when all three are 0 you want to use 50% of the Theoretical Capacity (D23 × C28), then put this in D30:

=IF(SUM(D2527)=0, D23*C28, SUM(D2527))

• SUM(D2527)=0 → no measurable ventilation ⇒ return D23 * C28 (your 50% case).
• Otherwise ⇒ return the total of D2527.

If you want to ignore any negative or text values and only add positive entries:

=IF(SUM(D2527)=0, D23*C28, SUMIFS(D2527, D2527, ">0"))

That should give you the behavior you described and keep D30 as the final ventilation capacity.

 

[Lee]

That is perfect thank you very much, I have leaned a lot since the short time on this group.

 

[shamimarita]

You are most welcome. Glad to hear that! Happy you’re finding value in the group. Keep learning and exploring with ExcelDemy!