Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 127 posts ]  Go to page Previous  1, 2, 3  Next
Author Message
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 15:59 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
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)


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:23 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Holy shit

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:25 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Is Joans trying to hack the forum?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:26 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
You sunk my Battleship.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:29 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 12034
Location: Sunny Stoke
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 :)

_________________
Consolemad | Under Logic
Curse, the day is long
Realise you don't belong


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:29 
User avatar
EvilTrousers

Joined: 30th Mar, 2008
Posts: 3073
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.

_________________
Everyone but Zardoz is better than me at videogames.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:32 
User avatar
Heavy Metal Tough Guy

Joined: 31st Mar, 2008
Posts: 6496
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:37 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
... IFnet fights back.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:40 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
I did say it wasn't elegant.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:43 
User avatar

Joined: 12th Apr, 2008
Posts: 17757
Location: Oxford
TheVision wrote:
You sunk my Battleship.


:DD


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:44 
User avatar

Joined: 12th Apr, 2008
Posts: 17757
Location: Oxford
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 17:01 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 18:08 
User avatar

Joined: 12th Apr, 2008
Posts: 17757
Location: Oxford
It's character-building.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 19:18 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
Take out a bracket.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 6:23 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 8:28 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
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."

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:02 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
Oh, what version of excel is it, maybe they've changed a limit. Or it needs to be an xlsx file, rather than xls.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:30 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
Well, the formula does something :)

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:31 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:36 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
Bobbyaro wrote:
Well, the formula does something :)


In theory, it gives you the total rate for each period, but I never actually tested it. :p

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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:42 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:44 
User avatar

Joined: 27th Mar, 2008
Posts: 25549
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.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 11:06 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 4296
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 11:12 
User avatar
Heavy Metal Tough Guy

Joined: 31st Mar, 2008
Posts: 6496
Probably! There are serious Excel competitions, certainly.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 11:20 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 4296
Squirt wrote:
Probably! There are serious Excel competitions, certainly.


Yowzers.

https://techcommunity.microsoft.com/t5/Excel-World-Champ/ExcelWorldChamp-Awards-Show/td-p/55315


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Feb 27, 2018 20:18 
User avatar
UltraMod

Joined: 27th Mar, 2008
Posts: 55715
Location: California
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.

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Feb 27, 2018 20:30 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 12034
Location: Sunny Stoke
You'll want the COUNTIF function I think.

_________________
Consolemad | Under Logic
Curse, the day is long
Realise you don't belong


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 28, 2018 16:02 
User avatar
UltraMod

Joined: 27th Mar, 2008
Posts: 55715
Location: California
devilman wrote:
You'll want the COUNTIF function I think.

I can't seem to get that function working at all. :(

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 28, 2018 16:05 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Code:
=COUNTIF(haystack, needle)

so assuming you're looking for '1' in the A column

Code:
=COUNTIF(A:A, 1)

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 28, 2018 16:08 
User avatar
UltraMod

Joined: 27th Mar, 2008
Posts: 55715
Location: California
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)

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 28, 2018 16:14 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
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"

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 28, 2018 16:16 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Like this:

Code:
=COUNTIFS(Sheet1!$A$1:$A$14,"<=10",Sheet1!$A$1:$A$14,">=1")

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 28, 2018 16:17 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 28, 2018 18:59 
User avatar
UltraMod

Joined: 27th Mar, 2008
Posts: 55715
Location: California
I could kiss you, Cras! That works like a charm.

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Feb 28, 2019 18:16 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 10910
Location: Devon
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:

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



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

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Feb 28, 2019 18:31 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
If you're looking up B2 in A2:A6, you can use
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.


You do not have the required permissions to view the files attached to this post.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Feb 28, 2019 20:15 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 10910
Location: Devon
Grim... wrote:
If you're looking up B2 in A2:A6, you can use
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.

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Mar 01, 2019 10:46 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 10910
Location: Devon
Thanks Grim... that nearly worked perfectly!

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


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.)

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Mar 01, 2019 10:51 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Mar 01, 2019 10:54 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 10910
Location: Devon
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.

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Oct 03, 2019 14:29 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
I have a spreadsheet like so:
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.


You do not have the required permissions to view the files attached to this post.

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Oct 03, 2019 14:37 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 10910
Location: Devon
Can you use either vlookup or hlookup? I am sure I've done similar before.

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Oct 03, 2019 14:53 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Can you put the same text in both cells instead of having a merged cell?

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Oct 03, 2019 14:59 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
unfortunately not, it isn't my spreadsheet, but even then, that would only return the first value, wouldn't it?

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Oct 03, 2019 15:05 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
What's in cells A19 and D18 of the current sheet?


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Oct 03, 2019 15:29 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
the project 'title' ("Ops inventory") and the month ("Jul-19")

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Oct 03, 2019 15:48 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
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?


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Feb 18, 2020 18:28 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
https://www.triplem.com.au/story/there- ... eets-85915

Lads, there's a World Championship For Excel Spreadsheets

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Feb 18, 2020 20:33 
User avatar

Joined: 30th Mar, 2008
Posts: 14130
Location: Shropshire, UK
Only Excel? Psh. As National IT User of the Year (2001), I'm clearly better than they are.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Feb 18, 2020 20:49 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Also demonstrating serious proficiency with excel usually involves saying "don't use Excel for this"

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
Display posts from previous:  Sort by  
Reply to topic  [ 127 posts ]  Go to page Previous  1, 2, 3  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: Columbo and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search within this thread:
You are using the 'Ted' forum. Bill doesn't really exist any more. Bogus!
Want to help out with the hosting / advertising costs? That's very nice of you.
Are you on a mobile phone? Try http://beex.co.uk/m/
RIP, Owen. RIP, MrC.

Powered by a very Grim... version of phpBB © 2000, 2002, 2005, 2007 phpBB Group.