How to make a contingent formula shorter...
 

I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

Hi.

=MIN(SUM(B5:M5),24)

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"phooey" wrote in message
...
I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

Try this:

=MIN(SUM(B5:M5),24)

HTH,
Elkar


"phooey" wrote:

I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

Try =IF(SUM(B5:M5)=24,24,SUM(B5:M5))
or, without IF and even shorter
MIN(SUM(B5:M5),24)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"phooey" wrote in message
...
I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

=MIN(SUM(B5:M5),24)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"phooey" wrote in message ...
|I am calculating sick day accrual.
| It's cumulative and adds hours each month, to a max of 24 hours.
|
| Here's what I have, and it works:
| =IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)
|
| I would like to make this shorter.
| I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.
|
| Ideas??
|
| I Know.. if it's not broke don't fix it.
| But I know there has to be an easier/shorter way.....
|
|

How to make a contingent formula shorter...
 

Try this

=MIN(24,SUM(B5:M5))


Regards,

Peo Sjoblom

phooey wrote:
I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

Didn't work. It returns a value of '0' (zero).

Here's what I'm using it on (using my formula)
The employee SS I tested this on has 12 accrued hours over 4 months.
ie) b5=4, c5=4, d5=4, e5=4, and f5-m5 are all 0
As I add hours to colums f-m it increases the total, to a max of 24.

When I use your formula it returns 0.

Ideas?

p.s. thanks for helping :-)

"Elkar" wrote:

Try this:

=MIN(SUM(B5:M5),24)

HTH,
Elkar


"phooey" wrote:

I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

BAH!!!

I entered your formula on the worng line.. hence the 0 return
**smacks self in head**

When I use your formula on the correct line.. hence b5 on line 5, and not
line 8 *smacks self again* it works great.

Thanks again!!

And thanks to everyone else who posted.... :-)


"phooey" wrote:

Didn't work. It returns a value of '0' (zero).

Here's what I'm using it on (using my formula)
The employee SS I tested this on has 12 accrued hours over 4 months.
ie) b5=4, c5=4, d5=4, e5=4, and f5-m5 are all 0
As I add hours to colums f-m it increases the total, to a max of 24.

When I use your formula it returns 0.

Ideas?

p.s. thanks for helping :-)

"Elkar" wrote:

Try this:

=MIN(SUM(B5:M5),24)

HTH,
Elkar


"phooey" wrote:

I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

Perhaps your numbers are stored as a Text format rather than Number. This
would explain why the SUM function ignores them. Try this instead:

=MIN(SUM(VALUE(B5:M5)),24)

This is an array formula, and must be entered with CTRL-SHIFT-ENTER instead
of just Enter. If done properly, the formula should be surrounded by { }.

HTH,
Elkar



"phooey" wrote:

Didn't work. It returns a value of '0' (zero).

Here's what I'm using it on (using my formula)
The employee SS I tested this on has 12 accrued hours over 4 months.
ie) b5=4, c5=4, d5=4, e5=4, and f5-m5 are all 0
As I add hours to colums f-m it increases the total, to a max of 24.

When I use your formula it returns 0.

Ideas?

p.s. thanks for helping :-)

"Elkar" wrote:

Try this:

=MIN(SUM(B5:M5),24)

HTH,
Elkar


"phooey" wrote:

I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

Bah!!!
** Smacks self in head **

I put the formula on the wrong line. When I put your formula on the correct
line... ie B5 on line 5, and not line 8, it works great *smacks self again*

Thanks again for your help, and thanks to everyone for your quick replies.
:-)


"phooey" wrote:

Didn't work. It returns a value of '0' (zero).

Here's what I'm using it on (using my formula)
The employee SS I tested this on has 12 accrued hours over 4 months.
ie) b5=4, c5=4, d5=4, e5=4, and f5-m5 are all 0
As I add hours to colums f-m it increases the total, to a max of 24.

When I use your formula it returns 0.

Ideas?

p.s. thanks for helping :-)

"Elkar" wrote:

Try this:

=MIN(SUM(B5:M5),24)

HTH,
Elkar


"phooey" wrote:

I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....

How to make a contingent formula shorter...
 

I think our posts 'crossed in the mail' and you missed my earlier reply.

here it is...

/quote/
Bah!!!
** Smacks self in head **

I put the formula on the wrong line. When I put your formula on the correct
line... ie B5 on line 5, and not line 8, it works great *smacks self again*

Thanks again for your help, and thanks to everyone for your quick replies.
:-)
/unquote/

Im such a goof.... I can't believe I forgot to change the numbers to
coincide with the line I was working on....

Thanks again..

"Elkar" wrote:

Perhaps your numbers are stored as a Text format rather than Number. This
would explain why the SUM function ignores them. Try this instead:

=MIN(SUM(VALUE(B5:M5)),24)

This is an array formula, and must be entered with CTRL-SHIFT-ENTER instead
of just Enter. If done properly, the formula should be surrounded by { }.

HTH,
Elkar



"phooey" wrote:

Didn't work. It returns a value of '0' (zero).

Here's what I'm using it on (using my formula)
The employee SS I tested this on has 12 accrued hours over 4 months.
ie) b5=4, c5=4, d5=4, e5=4, and f5-m5 are all 0
As I add hours to colums f-m it increases the total, to a max of 24.

When I use your formula it returns 0.

Ideas?

p.s. thanks for helping :-)

"Elkar" wrote:

Try this:

=MIN(SUM(B5:M5),24)

HTH,
Elkar


"phooey" wrote:

I am calculating sick day accrual.
It's cumulative and adds hours each month, to a max of 24 hours.

Here's what I have, and it works:
=IF(B5+C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5=24,24,B5+ C5+D5+E5+F5+G5+H5+I5+J5+K5+L5+M5)

I would like to make this shorter.
I've tried using: =IF(B5:M5=24,24,B5:M5), but it errors.

Ideas??

I Know.. if it's not broke don't fix it.
But I know there has to be an easier/shorter way.....