If Formula

[natei6]

Hi Frank,

That didn't Work.

When I tried =IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12), it worked i
B1 was greater than zero. I think I need a formula that will do thi
=MIN(SUM(B2:B8),40/24) if B1 is empty.

I think we're close

Nathan Sargean

 

[Frank Kabel]

Hi
sorry my fault. Try
=IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12*(B1>0))

 

[natei6]

Hi Frank,

It works!, almost. Is there a way to make it so it won't allo
negative numbers? If less than zero, ""?

=IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12*(B1>0))

Thanks a Million,

Nathan Sargean

 

[Frank Kabel]

Do you mean to prevent calculation if B1 is <0? If yes try
=IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12*(B1>0))*(B1>=0)

will return '0' if B1 is smaller than zero

 

[natei6]

Hi Frank,
Thanks, I added *(B9>80=0) and it worked.

Thanks for all your patience you have been most helpful.

All the best!

Nathan Sargean

 

[natei6]

Hi Frank,

How can I add to this formula to equal 0 if B9 is equal to or greate
than 80?

=IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12*(B1>0))*(B1>=0)


All the best,

Nathan Sargean

 

[Frank Kabel]

Hi
not fully tested but try
=IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12*(B1>0))*(B1>=0)*(B9<80)

 

[natei6]

Hi Frank,
I still got the same result. I get a negative number in if B9 i
greater than 80. Any more suggestions?

All the Best,
Nathan Sargeant
Hi
not fully tested but try
=IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12*(B1>0))*(B1>=0)*(B9<80)

 

[Frank Kabel]

Hi
you asked in your previous post for a condition if B9 is greater 80. If
you want to compare B1 use
=IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12*(B1>0))*(B1>=0)*(B1<80)

 

[natei6]

Hi,

Thankyou, I needed B9. At first it didn't work so I added /24 and
got the desired result.

You've been most helpful,

Nathan Sargean