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)
=((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)
Holy shit
Is Joans trying to hack the forum?
You sunk my Battleship.
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
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.
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.
... IFnet fights back.
I did say it wasn't elegant.
TheVision wrote:
You sunk my Battleship.
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.
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.
It's character-building.
Take out a bracket.
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:
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.
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
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.
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."
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."
Oh, what version of excel is it, maybe they've changed a limit. Or it needs to be an xlsx file, rather than xls.
Well, the formula does something
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
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
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.
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.
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.
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.
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
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.
Probably! There are serious Excel competitions, certainly.
Squirt wrote:
Probably! There are serious Excel competitions, certainly.
Yowzers.
https://techcommunity.microsoft.com/t5/Excel-World-Champ/ExcelWorldChamp-Awards-Show/td-p/55315
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:
Help me, excellent friends.
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))
=(IF('Sheet A'!E:E<=5,1,0))
=(IF('Sheet A'!E:E<=10,1,0))
Help me, excellent friends.
You'll want the COUNTIF function I think.
devilman wrote:
You'll want the COUNTIF function I think.
I can't seem to get that function working at all.
Code:
=COUNTIF(haystack, needle)
so assuming you're looking for '1' in the A column
Code:
=COUNTIF(A:A, 1)
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)
If those numbers are right, you can use "<=5" or "<=10" though that gives you 0-5 not 1-5.
You can use COUNTIFS to specify multiple criteria, so specify the first criteria as ">=1" and the second as "<=5"
You can use COUNTIFS to specify multiple criteria, so specify the first criteria as ">=1" and the second as "<=5"
Like this:
Code:
=COUNTIFS(Sheet1!$A$1:$A$14,"<=10",Sheet1!$A$1:$A$14,">=1")
I believe my gaming spreadsheet does this to keep track of my average cost of a game.
I didn't do it though. One of you lot helped me out.
I didn't do it though. One of you lot helped me out.
I could kiss you, Cras! That works like a charm.
Can anyone suggest a formula that would compare a value in an expected total column against an actual total column and then work out how many rows behind it is?
For example:
I essentially want another column, that says it's round 10 and we've only got 80, that's the total we should have by round 6 so we are 4 rounds behind target. Does that make sense? I'm guessing it will contain a vlookup of sorts, but can that 'round down' in that way?
Any help appreciated.
Thanks
For example:
Code:
Round Expected Actual Total
1 10 5
2 20 10
3 35 15
4 40 20
5 55 25
6 79 30
7 96 40
8 110 50
9 134 70
10 150 80
1 10 5
2 20 10
3 35 15
4 40 20
5 55 25
6 79 30
7 96 40
8 110 50
9 134 70
10 150 80
I essentially want another column, that says it's round 10 and we've only got 80, that's the total we should have by round 6 so we are 4 rounds behind target. Does that make sense? I'm guessing it will contain a vlookup of sorts, but can that 'round down' in that way?
Any help appreciated.
Thanks
If you're looking up B2 in A2:A6, you can use
I've attached a file.
Code:
=MATCH(SMALL(A$2:A$7,COUNTIF(A$2:A$7,"<"&B2)+1),A$2:A$7,0)
to get the row with the closest number without going over.I've attached a file.
Grim... wrote:
If you're looking up B2 in A2:A6, you can use
I've attached a file.
Code:
=MATCH(SMALL(A$2:A$7,COUNTIF(A$2:A$7,"<"&B2)+1),A$2:A$7,0)
to get the row with the closest number without going over.I've attached a file.
Thanks, I'll give that a go in the morning.
Thanks Grim... that nearly worked perfectly!
This is the cell content at the moment on row 61
=IF(A61<LastRound,(MATCH(SMALL(J$2:J$366,COUNTIF(J$2:J$366,"<"&I61)),J$2:J$366,0))-D61,"")
I61 = 691
D61 = 60
J36 - J38 is
so picks up J37 from that and returns 36
36 - 60 = -24 and that's what is in the cell. Perfect.
However, the first X rows in column I are all 0 and they all return #NUM!
I thought it must be down to the 0, so tried starting it on 1, but that had no effect either (It's no biggy it's in the past, but I'm just curious as to why it can't cope with it at the moment.)
This is the cell content at the moment on row 61
Code:
=IF(A61<LastRound,(MATCH(SMALL(J$2:J$366,COUNTIF(J$2:J$366,"<"&I61)),J$2:J$366,0))-D61,"")
I61 = 691
D61 = 60
J36 - J38 is
Code:
630
666
703
666
703
so picks up J37 from that and returns 36
36 - 60 = -24 and that's what is in the cell. Perfect.
However, the first X rows in column I are all 0 and they all return #NUM!
I thought it must be down to the 0, so tried starting it on 1, but that had no effect either (It's no biggy it's in the past, but I'm just curious as to why it can't cope with it at the moment.)
Is the Actual Total lower than anything in the Expected column? It might be that it can't find a row smaller than the number it's looking for.
Grim... wrote:
Is the Actual Total lower than anything in the Expected column? It might be that it can't find a row smaller than the number it's looking for.
I thought it was that so added a row with an expected total of 0, I guess from what you're saying I need to add an expected total of -1? As it's in the past my fudge for those rows is fine for my purposes. Thanks again for the help.
I have a spreadsheet like so:
I have a lookup from another file:
=INDEX('[FY20_Wiesbaden Site.xlsx]KPIs'!$K$6:$X$113, MATCH(A19, '[FY20_Wiesbaden Site.xlsx]KPIs'!$D$6:$D$113, 0), MATCH(D$18, '[FY20_Wiesbaden Site.xlsx]KPIs'!$K$5:$W$5, 0))
that return the point where the txt "Ops Inventory" (variable) converge with the month. but it returns the "target" value.
I also want to return the "actual", but I have no reference value for the row as the the cell with "Ops inventory" in it is merged.
How do I return both values to two different cells?
I can't get offset to work.
Attachment:
offset.JPG
I have a lookup from another file:
=INDEX('[FY20_Wiesbaden Site.xlsx]KPIs'!$K$6:$X$113, MATCH(A19, '[FY20_Wiesbaden Site.xlsx]KPIs'!$D$6:$D$113, 0), MATCH(D$18, '[FY20_Wiesbaden Site.xlsx]KPIs'!$K$5:$W$5, 0))
that return the point where the txt "Ops Inventory" (variable) converge with the month. but it returns the "target" value.
I also want to return the "actual", but I have no reference value for the row as the the cell with "Ops inventory" in it is merged.
How do I return both values to two different cells?
I can't get offset to work.
Can you use either vlookup or hlookup? I am sure I've done similar before.
Can you put the same text in both cells instead of having a merged cell?
unfortunately not, it isn't my spreadsheet, but even then, that would only return the first value, wouldn't it?
What's in cells A19 and D18 of the current sheet?
the project 'title' ("Ops inventory") and the month ("Jul-19")
Can you work out what it's matching from the other spreadsheet?
I'm a bit confused here as match should give you a row number, but the range it's looking up only covers a single row - as a wild stab in the dark, does changing $K$5:$W$5 to $K$6:$W$6 give you the actual results?
I'm a bit confused here as match should give you a row number, but the range it's looking up only covers a single row - as a wild stab in the dark, does changing $K$5:$W$5 to $K$6:$W$6 give you the actual results?
https://www.triplem.com.au/story/there- ... eets-85915
Lads, there's a World Championship For Excel Spreadsheets
Lads, there's a World Championship For Excel Spreadsheets
Only Excel? Psh. As National IT User of the Year (2001), I'm clearly better than they are.