Thread Options 
|
#1716722 - 07/03/12 03:16 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Aug 2006
Posts: 6,577
Down Yonder
|
Cool! =IF(F1288<0,F1288/D1288,F1288/E1288) should work for every scenario then. Sorry I led you around the world to go next door  Now, since you were ready to take Happy's advice and pay for an analyst...
_________________________
...not only will I do it for you, I... I... I... yes, yes, I'll do it for you.
|
|
Return to Top
|
|
|
|
|
#1716745 - 07/03/12 03:32 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Aug 2006
Posts: 6,577
Down Yonder
|
Sweet! I'll try not to spend them all at once.
_________________________
...not only will I do it for you, I... I... I... yes, yes, I'll do it for you.
|
|
Return to Top
|
|
|
|
|
#1716749 - 07/03/12 03:34 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Aug 2006
Posts: 6,577
Down Yonder
|
<--- still trying to get her ^^^^ number
_________________________
...not only will I do it for you, I... I... I... yes, yes, I'll do it for you.
|
|
Return to Top
|
|
|
|
|
#1716754 - 07/03/12 03:37 PM
Re: EXCEL formula help
Bobby Boucher
|
10K Club
Joined: Jul 2002
Posts: 13,994
|
this is too easy
_________________________
blah
|
|
Return to Top
|
|
|
|
|
#1716756 - 07/03/12 03:40 PM
Re: EXCEL formula help
Peepers
|
10K Club
Joined: Aug 2005
Posts: 18,789
TX
|
|
|
Return to Top
|
|
|
|
|
#1716758 - 07/03/12 03:42 PM
Re: EXCEL formula help
Miscuit
|
10K Club
Joined: Jul 2004
Posts: 18,990
|
Miscuit's good with a number.
_________________________
With the lights out, it's less dangerous.
|
|
Return to Top
|
|
|
|
|
#1716760 - 07/03/12 03:42 PM
Re: EXCEL formula help
A_G
|
10K Club
Joined: Jul 2002
Posts: 13,994
|
speak and spell acting up again?
_________________________
blah
|
|
Return to Top
|
|
|
|
|
#1716761 - 07/03/12 03:43 PM
Re: EXCEL formula help
Peepers
|
10K Club
Joined: Jul 2004
Posts: 18,990
|
YOU CHANGED IT! 
_________________________
With the lights out, it's less dangerous.
|
|
Return to Top
|
|
|
|
|
#1716763 - 07/03/12 03:44 PM
Re: EXCEL formula help
A_G
|
10K Club
Joined: Aug 2005
Posts: 18,789
TX
|
|
|
Return to Top
|
|
|
|
|
#1725309 - 07/31/12 03:29 PM
Re: EXCEL formula help
Miscuit
|
Power Poster
Joined: Jul 2007
Posts: 3,298
only if I want to....
|
Hey guys...need some of that smartness help again I am looking at a Payroll excel sheet. I did the best I could to put Monday thru Sunday as my example below and a column showing Regular Time and OT I need the Regular Time to always calculate to at least 40 if the employee gets that many hours (or the actual figure they worked...like say 32 hours...the column would say 32), but if they worked 64 hours like in the first row, it would need to say 40 hours with the remainder going to OT coulmn. I think I may have the OT column figured out...this is what I put... =IF(H2>40,H2-40) It's the Regular Time getting it to only say actual hours not to exceed the 40 for that column. Am I making any sense on how I'm typing this...cause I feel totally  M T W T F ST SN RT OT 10 10 10 10 10 8 6 40 24 10 5 6 10 6 6 12 40 15 10 7 5 10 5 5 2 40 4 Thanks a million for any help I can get! 
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6
|
|
Return to Top
|
|
|
|
|
#1725321 - 07/31/12 03:43 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Mar 2005
Posts: 2,659
Kennel
|
Try this
=IF(SUM(A3:G3)>40,40,SUM(A3:G3))
_________________________
CAMS, AMLP, AKC, K-9
|
|
Return to Top
|
|
|
|
|
#1725349 - 07/31/12 04:04 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Aug 2006
Posts: 6,577
Down Yonder
|
Try this
=IF(SUM(A3:G3)>40,40,SUM(A3:G3)) That^^^ will work for your RT column. But since you're changing this column I think you'll have to change the formula for your OT column also. Something like =IF(SUM(A3:G3)>40,SUM(A3:G3)-40,0)
_________________________
...not only will I do it for you, I... I... I... yes, yes, I'll do it for you.
|
|
Return to Top
|
|
|
|
|
#1725354 - 07/31/12 04:08 PM
Re: EXCEL formula help
Big Dog
|
Power Poster
Joined: Jul 2007
Posts: 3,298
only if I want to....
|
Try this
=IF(SUM(A3:G3)>40,40,SUM(A3:G3)) Thanks so much Big Dog...that worked for my 40 column...but it ended up putting "false" in my overtime column. I need it to put the additional 24 hours in that column. This is the forumla I orginally had.... =IF(h3>40,h3-40) One of these days these if forumlas are going to totally click...I just know it
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6
|
|
Return to Top
|
|
|
|
|
#1725397 - 07/31/12 05:03 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Mar 2005
Posts: 2,659
Kennel
|
Glad to be of assistance, even if i am a little rusty with formulas.
_________________________
CAMS, AMLP, AKC, K-9
|
|
Return to Top
|
|
|
|
|
#1725528 - 07/31/12 07:05 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Nov 2008
Posts: 4,132
Somewhere in the middle
|
Ok, an Excel Novice, I know it well enought to get around, but need some assistance.
If i am given a spread sheet of account to test, and it has (just to keep this simple) 1000 accounts and I want to pick out and test evry 11th account starting with the 9 account on the list. Is there a formula or macro command I can use that will make Excel highlight or extract those accounts for me?
Or is the only way to get the sampling I want is to count and copy every 11th account?
_________________________
I'm only responsible for what I say, not for what you understand.
|
|
Return to Top
|
|
|
|
|
#1725537 - 07/31/12 07:15 PM
Re: EXCEL formula help
DD Regs
|
Power Poster
Joined: Mar 2005
Posts: 2,659
Kennel
|
Way back, when i was an auditor, I found the simple method was to have a separate column for "Selection". I would start at the first account selected and place an "X", count down the appropriate lines and put another "X", and keep repeating till done. Then sort the Selection column and put all the X's together.
_________________________
CAMS, AMLP, AKC, K-9
|
|
Return to Top
|
|
|
|
|
#1725544 - 07/31/12 07:20 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Nov 2008
Posts: 4,132
Somewhere in the middle
|
QCL, Andy's tool is what I use to get what I want sample, but it doesn't pull the items from the list for me, it just tells me the method of which ones to choose.
Big dog, that still required a lot of "work", when your list of accounts size is in the 1000's.
There's got to be a better/easier way.
_________________________
I'm only responsible for what I say, not for what you understand.
|
|
Return to Top
|
|
|
|
|
#1725554 - 07/31/12 07:25 PM
Re: EXCEL formula help
KTMiteComply
|
Diamond Poster
Joined: Jun 2005
Posts: 1,343
|
I used to use the formula =RAND()<=.1
That would give me about a 10% sample, give or take. Make a column for the formula, type that in the first row, then copy the formula all the way down your list. That will give you TRUE and FALSE responses. Then you can filter or sort your list. You do need to be careful though, as they will change. I always created another column, and did a copy and paste special, just copying the values (not the forumulas) over to the new column.
_________________________
CRCM
|
|
Return to Top
|
|
|
|
|
#1732129 - 08/17/12 01:17 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Jul 2007
Posts: 3,298
only if I want to....
|
Hey guys...need some of that smartness help again I am looking at a Payroll excel sheet. I did the best I could to put Monday thru Sunday as my example below and a column showing Regular Time and OT I need the Regular Time to always calculate to at least 40 if the employee gets that many hours (or the actual figure they worked...like say 32 hours...the column would say 32), but if they worked 64 hours like in the first row, it would need to say 40 hours with the remainder going to OT coulmn. I think I may have the OT column figured out...this is what I put... =IF(H2>40,H2-40) It's the Regular Time getting it to only say actual hours not to exceed the 40 for that column. Am I making any sense on how I'm typing this...cause I feel totally M T W T F ST SN RT OT 10 10 10 10 10 8 6 40 24 10 5 6 10 6 6 12 40 15 10 7 5 10 5 5 2 40 4 Thanks a million for any help I can get! I've been putting this off for weeks because I'm scared somebody might take me out to the shed cause I keep on asking these questions  LOL!!! Ok...so here is the new "delimma" in this sheet I've been asked to create...and like all the others...I just can't for the life of me get this. Monday - Friday is regular time unless it goes over 40 hours, then of course it would be considered "overtime". HOwever, Sat & Sun is ALWAYS considered overtime irregardless if Monday - Friday you've only worked 20 hours. As always any help at all is more than greatly appreciated!!! Happy Friday!!
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6
|
|
Return to Top
|
|
|
|
|
#1732147 - 08/17/12 01:33 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
Joined: Mar 2005
Posts: 2,659
Kennel
|
Just remove those days from your "if" statement and add the days to the OT total.
_________________________
CAMS, AMLP, AKC, K-9
|
|
Return to Top
|
|
|
|
|
|
Previous Thread
Index