Question for Microsoft Excel users

[BrueThru]

I am trying to find out how to do the following for a time sheet:

Cell J1 =sum(b1:g1)

I want J1 to stop at 40 and everything over that falls into K1

Any gurus out there?

 

[ajskillz13]

What do you mean by stop at 40? The total of the sum?

 

[ajskillz13]

J1: =if(sum(b1:g1)<40,sum(b1:g1),40)
K1: =if(sum(b1:g1)>40,sum(b1:g1)-40,0)

This will help you include the total up to 40 in J1 and any excess in K1. Hope that's what you're looking for.

 

[RooKie]

On J1 use this:
=IF(SUM(B1,G1)>40,40,SUM(B1,G1))

On K1 use:
=IF(SUM(B1,G1)>40,SUM(B1,G1)-40,0)

There may be more efficient ways of doing this, but it gets the job done

 

[ajskillz13]

On J1 use this:
=IF(SUM(B1,G1)>40,40,SUM(B1,G1))

On K1 use:
=IF(SUM(B1,G1)>40,SUM(B1,G1)-40,0)

There may be more efficient ways of doing this, but it gets the job done

Great minds think alike! Almost at least!

 

[RooKie]

J1: =if(sum(b1:g1)<40,sum(b1:g1),40)
K1: =if(sum(b1:g1)>40,sum(b1:g1)-40,0)

This will help you include the total up to 40 in J1 and any excess in K1. Hope that's what you're looking for.

Great minds think alike Hahaha

 

[RooKie]

great minds think alike! Almost at least!

GET OUT OF MY HEAD!!!!

 

[JSP]

ahahah, you two freaks! Thats a little crazy.

 

[ajskillz13]

Does our two hour time difference count as a parallel universe??? I don't want to know any more details. You already have a copy of my bike. Getting scared!

 

[RooKie]

Does our two hour time difference count as a parallel universe??? I don't want to know any more details. You already have a copy of my bike. Getting scared!

 

[Blue-Sun]

As I am agreeing with posters upthread, I'd like to add there is also a function formula wizard in some versions of Excel that can help you write these IF, Then, Else statements in your spreadsheet. It's a big help and allows the user to walk through the logic flow to make sure they have written the formula correctly.

Just my two cents...

 

[CrazyCawi]

Does our two hour time difference count as a parallel universe??? I don't want to know any more details. You already have a copy of my bike. Getting scared!

thats kinda creepy huh

 

[BrueThru]

Much appreciated guys.

You guys are very smart. :thumbup:

I went with:

column K
=SUM(B3:G3)-L3

column L
=IF(SUM(B3:G3)>40,SUM(B3:G3)-40,0)

Works great.

Nice to see the variations you guys came up with.

EDIT: Gave the two of you some REP for the help. (ajskillz and Rookie)

 

[CrazyCawi]

Much appreciated guys.

You guys are very smart. :thumbup:

I went with:

column K
=SUM(B3:G3)-L3

column L
=IF(SUM(B3:G3)>40,SUM(B3:G3)-40,0)

Works great.

Nice to see the variations you guys came up with.

EDIT: Gave the two of you some REP for the help. (ajskillz and Rookie)