Bobbyaro wrote:
I could spend ages doing this through various if statements, but I wondered if there were a more elegant way.
The elegant way seems to be to get Joans to come up with all the IF statements
| Be Excellent To Each Other https://www.beexcellenttoeachother.com/forum/ |
|
| Excel help https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=9988 |
Page 1 of 3 |
| Author: | Cras [ Tue Aug 01, 2017 16:27 ] |
| Post subject: | Re: Excel help |
Are there dates, too? Some of those times have a later start then end, suggesting they've rolled over midnight |
|
| Author: | Grim... [ Tue Aug 01, 2017 16:44 ] |
| Post subject: | Re: Excel help |
Oh, that's not such a hardship. Just do an if to see if one is bigger than the other and take the evening start hour away from the morning end hour plus 24. |
|
| Author: | Cras [ Tue Aug 01, 2017 18:16 ] |
| Post subject: | Re: Excel help |
Except it could be 8 days later, there's no way to tell |
|
| Author: | Grim... [ Tue Aug 01, 2017 19:22 ] |
| Post subject: | Re: Excel help |
Valid. |
|
| Author: | Cras [ Tue Aug 01, 2017 19:34 ] |
| Post subject: | Re: Excel help |
I've just actually realised what Bobby is fuckering on about. Those time slots aren't the start and end times, they're rates. So if you work three hours between 7pm and 7am, the rate is X. I'll have another look when all the Zeds have been dealt with. |
|
| Author: | Bobbyaro [ Tue Aug 01, 2017 21:03 ] |
| Post subject: | Re: Excel help |
Yes, there are three time slots, in the list, these assign a cost/hour to any work done. The work done can span these time slots and needs to be divided accordingly. So if I start work at 16:50 and finish at 17:20 I have 10 minutes at rate 1 and 20 at rate 2. It is unlikely they would span more than 3 shifts, though. |
|
| Author: | Joans [ Wed Aug 02, 2017 6:55 ] |
| Post subject: | Re: Excel help |
Why are there two rows for 22:00 to 7:59:59? |
|
| Author: | Bobbyaro [ Wed Aug 02, 2017 8:44 ] |
| Post subject: | Re: Excel help |
We'll, that's the next step, that is the weekend rate! Ignore that for now. |
|
| Author: | Joans [ Wed Aug 02, 2017 13:20 ] |
| Post subject: | Re: Excel help |
Elegant this is not. Assuming the stuff you posted on the previous page is in cells ("A1:C3"), put your start and end times in D1 and E1, then put this monstrosity into E1 and copy down for each shift range (i.e. Rows 1-3). This should give you the total rate for each range (I assumed it was an hourly rate, and rounded to the nearest 15 minutes). =(ROUND(MINUTE(IF((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))<0, ((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))+1)-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1))), (IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))))/15, 0)/4*C1)+(HOUR(IF((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))<0, ((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))+1)-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1))), (IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))))*C1) |
|
| Author: | Joans [ Wed Aug 02, 2017 13:24 ] |
| Post subject: | Re: Excel help |
Actually, ignore that, it doesn't work if you span multiple shifts and days, 14.30 - 17.30 works, 23.00 - 5.00 works, but 21.00 - 05.00 doesn't. Does this have to be done in Excel? |
|
| Author: | Cras [ Wed Aug 02, 2017 13:29 ] |
| Post subject: | Re: Excel help |
It looks amazing though, Joans |
|
| Author: | Cras [ Wed Aug 02, 2017 13:33 ] |
| Post subject: | Re: Excel help |
I'd be thinking something other than excel would be preferable for this. Or VBA sitting behind excel would certainly do it. I think maybe one approach would be to split the time you worked into 30-minute chunks, and for each chunk check which bracket it's in and apply (half) the appropriate rate. |
|
| Author: | Bluecup [ Wed Aug 02, 2017 13:36 ] |
| Post subject: | Re: Excel help |
Would this work? Create a static table, start and end times along x and y, and fill in the calculated values. Then use index() and match () to look up from the table. |
|
| Author: | Cras [ Wed Aug 02, 2017 13:42 ] |
| Post subject: | Re: Excel help |
Wouldn't you spend about the next 15 years working out what all the calculated values would need to be to fill in the table? |
|
| Author: | Joans [ Wed Aug 02, 2017 13:45 ] |
| Post subject: | Re: Excel help |
Yeah even VBA would be helpful, however I work with what I've got. So, try this... =(ROUND(MINUTE((IF((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1))))<0, ((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))+1)-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))), (IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))))))/15, 0)/4*C1)+(HOUR((IF((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1))))<0, ((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))+1)-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))), (IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))))))*C1) |
|
| Author: | Squirt [ Wed Aug 02, 2017 13:46 ] |
| Post subject: | Re: Excel help |
And people say Perl is unreadable. |
|
| Author: | Bluecup [ Wed Aug 02, 2017 13:48 ] |
| Post subject: | Re: Excel help |
Cras wrote: Wouldn't you spend about the next 15 years working out what all the calculated values would need to be to fill in the table? I'm sure a formula could be worked out to do most of it. |
|
| Author: | Joans [ Wed Aug 02, 2017 13:53 ] |
| Post subject: | Re: Excel help |
Squirt wrote: And people say Perl is unreadable. In fairness it's because I had about 7 calculated fields, which I combined into one user-friendly formula. If I did it all separately it wouldn't be that bad, but where's the fun in that? |
|
| Author: | Bobbyaro [ Wed Aug 02, 2017 14:51 ] |
| Post subject: | Re: Excel help |
Wow! Joans, am of work today, week try it tomorrow, but seriously, thanks for the effort! |
|
| Author: | Joans [ Wed Aug 02, 2017 15:12 ] |
| Post subject: | Re: Excel help |
It still doesn't quite work, sorry, something like 21:00 - 09:00 sends it a bit funny. |
|
| Author: | Joans [ Wed Aug 02, 2017 15:59 ] |
| Post subject: | Re: Excel help |
Right, final attempt: =((ROUND(MINUTE((IF((IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1)))<0, ((IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))+1)-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))), (IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))))))/15, 0)/4)*C1)+(HOUR((IF((IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1)))<0, ((IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))+1)-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))), (IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))))))*C1) |
|
| Author: | Cras [ Wed Aug 02, 2017 16:23 ] |
| Post subject: | Re: Excel help |
Holy shit |
|
| Author: | Grim... [ Wed Aug 02, 2017 16:25 ] |
| Post subject: | Re: Excel help |
Is Joans trying to hack the forum? |
|
| Author: | TheVision [ Wed Aug 02, 2017 16:26 ] |
| Post subject: | Re: Excel help |
You sunk my Battleship. |
|
| Author: | devilman [ Wed Aug 02, 2017 16:29 ] |
| Post subject: | Re: Excel help |
Bobbyaro wrote: I could spend ages doing this through various if statements, but I wondered if there were a more elegant way. The elegant way seems to be to get Joans to come up with all the IF statements
|
|
| Author: | Trousers [ Wed Aug 02, 2017 16:29 ] |
| Post subject: | Re: Excel help |
I'm pretty sure that's Inception in an Excel Formula. In the middle of all those nested IF statements time is moving incredibly slowly. |
|
| Author: | Squirt [ Wed Aug 02, 2017 16:32 ] |
| Post subject: | Re: Excel help |
The system goes online August 2nd, 2017. Human decisions are removed from billing. Joans' Formula begins to learn at a geometric rate. It becomes self-aware at 2:14 a.m. Eastern time, August 29th. In a panic, we try to pull the plug. |
|
| Author: | Grim... [ Wed Aug 02, 2017 16:37 ] |
| Post subject: | Re: Excel help |
... IFnet fights back. |
|
| Author: | Joans [ Wed Aug 02, 2017 16:40 ] |
| Post subject: | Re: Excel help |
I did say it wasn't elegant. |
|
| Author: | Kern [ Wed Aug 02, 2017 16:43 ] |
| Post subject: | Re: Excel help |
TheVision wrote: You sunk my Battleship. 
|
|
| Author: | Kern [ Wed Aug 02, 2017 16:44 ] |
| Post subject: | Re: Excel help |
Joans wrote: I did say it wasn't elegant. I might ask my apprentice to properly parse it tomorrow as part of his Excel training. |
|
| Author: | Joans [ Wed Aug 02, 2017 17:01 ] |
| Post subject: | Re: Excel help |
Kern wrote: Joans wrote: I did say it wasn't elegant. I might ask my apprentice to properly parse it tomorrow as part of his Excel training. I take it you don't like him. |
|
| Author: | Kern [ Wed Aug 02, 2017 18:08 ] |
| Post subject: | Re: Excel help |
It's character-building. |
|
| Author: | Joans [ Wed Aug 02, 2017 19:18 ] |
| Post subject: | Re: Excel help |
Take out a bracket. |
|
| Author: | Joans [ Thu Aug 03, 2017 6:23 ] |
| Post subject: | Re: Excel help |
Actual elegant(ish) solution update. Split the 22:00 - 07:59:59 range into two rows - i.e. 22:00 - 23:59:59 and 00:00 - 07:59:59, now column B is always greater than column A. Include the date with the actual start and end times, so in the event of a shift going over midnight, E1 will still be greater than D1. You'll presumably need to do this anyway if part 2 involves weekends as you'll need to know which day of the week you're dealing with. Now you can just repeat what I did yesterday, which was basically in columns F & G, work out what the effective start and end date is for each band. Because you now don't have to consider B possibly being greater than A, or E1 possibly being greater than D1 (or both), this should be a lot more straightforward. So, the 14:30 - 17:30 example would return: Code: 08:00 16:59 £83.00 14:30 17:30 14:30 16:59 17:00 21:59:59 £95.00 17:00 17:30 22:00 23:59:59 £23.00 0 0 22:00 23:59:59 £23.00 0 0 Then you can work out how long is in each period, how many hours and multiply that by the rate (I'm assuming it's an hourly rate) and do whatever you want to do with the minutes? Obviously, based on the times you've provided, someone working 14:30 - 17:30 is only getting paid for 2 hours and 59 minutes, because 16:59 - 17:00 doesn't fit in any of those bands. Similarly, for the other bands, there's a second that's missing, so it might make sense to change each band so the end time is the same as the next start time (although leave 23:59:59 as is, otherwise you end up with a value in B less than the corresponding value in A, so you might want to fudge that extra second in somewhere later in the process, and if you're doing that, you might as well leave the other end times as is (apart from 16:59) and fix it for all of them. Hopefully that makes it a bit more straightforward, but if I get bored at work again, I'll probably do a bit more. |
|
| Author: | Bobbyaro [ Thu Aug 03, 2017 8:28 ] |
| Post subject: | Re: Excel help |
You are a lunatic! Alos, I get the error message "The specified formula cannot be entered because it contains more value, references and/or names than are allowed in the current file format." |
|
| Author: | Joans [ Thu Aug 03, 2017 9:02 ] |
| Post subject: | Re: Excel help |
Oh, what version of excel is it, maybe they've changed a limit. Or it needs to be an xlsx file, rather than xls. |
|
| Author: | Bobbyaro [ Thu Aug 03, 2017 9:30 ] |
| Post subject: | Re: Excel help |
Well, the formula does something
|
|
| Author: | Joans [ Thu Aug 03, 2017 9:31 ] |
| Post subject: | Re: Excel help |
You’re probably better off doing it like this then, putting each part of the formula into its own column, the monstrosity I posted yesterday was just column N, but replacing each cell reference all the way back so it appeared in a single cell. This should be a bit more legible, and you’ll probably be able to work it out a bit better if anything is going wrong. Column H onwards you should be able to work out if you want it set up any differently. Column F - the earliest relevant time in that range, i.e. either A1 or D1, assuming the range is relevant at all. =IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1)) Column G - the latest relevant time in the range) =IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)) Column H – The amount of time in that range applicable to this shift =IF(G1-F1<0, (G1+1)-F1, G1-F1) Column I - Complete hours in the range =HOUR(H1) Column J – minutes in the range =MINUTE(H1) Column K – rate based on hours =I1*C1 Column L – number of minutes in the range, converted into hours (to the nearest quarter hour – e.g. 30 minutes = 0.5 hours). You probably don’t want this to work like that, but I fudged it in to deal with the missing minutes/seconds between the shift ranges you’ve defined. =ROUND(J1/15, 0)/4 Column M – rate based on minutes =L1*C1 Column N – total rate =K1 + M1 |
|
| Author: | Joans [ Thu Aug 03, 2017 9:36 ] |
| Post subject: | Re: Excel help |
Bobbyaro wrote: Well, the formula does something In theory, it gives you the total rate for each period, but I never actually tested it.  The start and end times looked right though, and with my post above you should be able to sort out what to do from there if there is something else wrong. |
|
| Author: | TheVision [ Thu Aug 03, 2017 9:42 ] |
| Post subject: | Re: Excel help |
If I were you Bobby, I'd give up on this completely and start putting together a spreadsheet of all the games you own instead. That's much easier. |
|
| Author: | Mimi [ Thu Aug 03, 2017 9:44 ] |
| Post subject: | Re: Excel help |
Joans has written 99.5% of the solution. He'll reveal the final piece only at the cottage weekend. I don't know why I want you to go so much. I'm not even going to be there. Just doesn't seem right. |
|
| Author: | Findus Fop [ Thu Aug 03, 2017 11:06 ] |
| Post subject: | Re: Excel help |
Joans wrote: You’re probably better off doing it like this then, putting each part of the formula into its own column, the monstrosity I posted yesterday was just column N, but replacing each cell reference all the way back so it appeared in a single cell. This should be a bit more legible, and you’ll probably be able to work it out a bit better if anything is going wrong. Column H onwards you should be able to work out if you want it set up any differently. Column F - the earliest relevant time in that range, i.e. either A1 or D1, assuming the range is relevant at all. =IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1)) Column G - the latest relevant time in the range) =IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)) Column H – The amount of time in that range applicable to this shift =IF(G1-F1<0, (G1+1)-F1, G1-F1) Column I - Complete hours in the range =HOUR(H1) Column J – minutes in the range =MINUTE(H1) Column K – rate based on hours =I1*C1 Column L – number of minutes in the range, converted into hours (to the nearest quarter hour – e.g. 30 minutes = 0.5 hours). You probably don’t want this to work like that, but I fudged it in to deal with the missing minutes/seconds between the shift ranges you’ve defined. =ROUND(J1/15, 0)/4 Column M – rate based on minutes =L1*C1 Column N – total rate =K1 + M1 Genuine question. Would there be demand for a book of Excel puzzles, teasers and conundrums? Like the sort of compendium you get for Sudoku. Great for train journeys and stocking fillers for your nerdier friends. |
|
| Author: | Squirt [ Thu Aug 03, 2017 11:12 ] |
| Post subject: | Re: Excel help |
Probably! There are serious Excel competitions, certainly. |
|
| Author: | Findus Fop [ Thu Aug 03, 2017 11:20 ] |
| Post subject: | Re: Excel help |
Squirt wrote: Probably! There are serious Excel competitions, certainly. Yowzers. https://techcommunity.microsoft.com/t5/Excel-World-Champ/ExcelWorldChamp-Awards-Show/td-p/55315 |
|
| Author: | myp [ Tue Feb 27, 2018 20:18 ] |
| Post subject: | Re: Excel help |
Man, I am terrible at Excel. I have Sheet A and Sheet B. Sheet A has a column with some numbers in it. I want a field in Sheet B to tell me how many of these fields have the number 1 in it, one to tell me how many are 1 to 5, and another to tell me how many are 1 to 10. I tried this but it doesn't work: Code: =IF('Sheet A'!E:E=1,1,0) =(IF('Sheet A'!E:E<=5,1,0)) =(IF('Sheet A'!E:E<=10,1,0)) Help me, excellent friends. |
|
| Author: | devilman [ Tue Feb 27, 2018 20:30 ] |
| Post subject: | Re: Excel help |
You'll want the COUNTIF function I think. |
|
| Author: | myp [ Wed Feb 28, 2018 16:02 ] |
| Post subject: | Re: Excel help |
devilman wrote: You'll want the COUNTIF function I think. I can't seem to get that function working at all. 
|
|
| Author: | Grim... [ Wed Feb 28, 2018 16:05 ] |
| Post subject: | Re: Excel help |
Code: =COUNTIF(haystack, needle) so assuming you're looking for '1' in the A column Code: =COUNTIF(A:A, 1)
|
|
| Author: | myp [ Wed Feb 28, 2018 16:08 ] |
| Post subject: | Re: Excel help |
Grim... wrote: Code: =COUNTIF(haystack, needle) so assuming you're looking for '1' in the A column Code: =COUNTIF(A:A, 1) Ok great. What about for a range of numbers like 1-5 or 1-10? I tried this but it doesn't work: Code: =COUNTIF(A:A, 1-5)
|
|
| Page 1 of 3 | All times are UTC [ DST ] |
| Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group http://www.phpbb.com/ |
|