Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 127 posts ]  Go to page 1, 2, 3  Next
Author Message
 Post subject: Excel help
PostPosted: Mon Mar 10, 2014 11:04 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
I need help with a forumla for my gaming spreadsheet.

I have column H which shows the price I paid for each game. Column I shows the date I bought that game.

I would like a cell that adds up how much I spent in 2014, another cell for 2013 and so on.

Any ideas?


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Mar 10, 2014 11:08 
8-Bit Champion
User avatar
Two heads are better than one

Joined: 16th Apr, 2008
Posts: 14483
Add a column (hide it if you want) using the formula YEARS(whateveryourdatecolumnis) which will give you the year - then a pivot on year + cost


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Mar 10, 2014 11:14 
8-Bit Champion
User avatar
Two heads are better than one

Joined: 16th Apr, 2008
Posts: 14483
So your data ends up looking like this

Attachment:
data.png


And the pivot gives you

Attachment:
pivot.png


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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Mar 10, 2014 11:58 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
That looks confusing and indeed, it is when I've tried to do it. I was hoping just to have one cell with 2014's amount in, another with 2013 and so on.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Mar 10, 2014 12:04 
8-Bit Champion
User avatar
Two heads are better than one

Joined: 16th Apr, 2008
Posts: 14483
TheVision wrote:
That looks confusing and indeed, it is when I've tried to do it. I was hoping just to have one cell with 2014's amount in, another with 2013 and so on.


You can of course hide all of this on another sheet and make the main one look simple but the other way would be to just sort by year and then have a total for the year in a separate line.

I'm assuming you never go back and add in stuff from a previous year so once done they are 'set' ?


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Mar 10, 2014 12:06 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
I will get to that point but at the moment no. I have found the odd receipt here and there which I've added on.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Mar 10, 2014 12:11 
8-Bit Champion
User avatar
Two heads are better than one

Joined: 16th Apr, 2008
Posts: 14483
TheVision wrote:
I will get to that point but at the moment no. I have found the odd receipt here and there which I've added on.


Then just having a total column at the end and having it sum all the ones up to that point would work fine - if you add a new line in for something old you've found you just re-sort and it will be included - it relies a lot more on you doing the work than the pivot but its at least simple


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Mar 10, 2014 14:36 
User avatar
ugvm'er at heart...

Joined: 4th Mar, 2010
Posts: 22255
Create the years column as column I, hide it, then do something like this

Code:
=SUMIF(I3:I8,"2014",H3:H8)


where I is your year column, and H is your total column.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Mar 10, 2014 15:23 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
Bingo! That's done it. Thanks both for your help.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Apr 12, 2017 15:50 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
I've got a great spreadsheet that does VLOOKUPS from about 6 other sheets saved on the network. On my laptop, it works fine. But, it never updates on my boss's when he opens it. Is there a reason for this?

_________________
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: Wed Apr 12, 2017 15:54 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 12034
Location: Sunny Stoke
MaliA wrote:
I've got a great spreadsheet that does VLOOKUPS from about 6 other sheets saved on the network. On my laptop, it works fine. But, it never updates on my boss's when he opens it. Is there a reason for this?


Maybe he has his network drives mapped differently? I think it would report the error somewhere though if it can't find one of the files it needs to perform the lookup though.

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Apr 12, 2017 21:31 
User avatar
Decapodian

Joined: 15th Oct, 2010
Posts: 5134
MaliA wrote:
I've got a great spreadsheet that does VLOOKUPS from about 6 other sheets saved on the network. On my laptop, it works fine. But, it never updates on my boss's when he opens it. Is there a reason for this?


Do the lookups refer to a unc path, or the drive letter? If the latter, check it's mapped to the same letter.

Also, in the trust centre, check that the box for "allow trusted network locations" is ticked and the network drive is listed as trusted. If not, you may end up with a load of REF! In your spreadsheet.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Apr 12, 2017 23:35 
User avatar
Can you dig it?

Joined: 5th Apr, 2008
Posts: 4662
Zaphod's answer is the 'cleanest' IMO.

Since learning to use pivot tables and the 'grouping' ability within pivot tables, I have felt like the excel boss at work. You should seriously learn to use pivot tables, that will put you ahead of easily 90% of people that list 'MS Office' on their CV

Although to be fair, when it comes to 1337 IT skillage, the bar is quite low at this place, so if I wasn't the 'best at computers' here it would be utter shame upon me.

_________________
rumours about the high quality of the butter reached Yerevan


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Apr 12, 2017 23:36 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
You appear to be answering a three year old question.

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Apr 13, 2017 2:10 
User avatar
Can you dig it?

Joined: 5th Apr, 2008
Posts: 4662
:DD lolz, I was just so excited about having learned something new that actually helped me to do some stuff at work heaps faster (trawling through month worth of QC data)

Although, I was more commenting on Zaphod's reply, so nerrrr

_________________
rumours about the high quality of the butter reached Yerevan


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Apr 13, 2017 8:59 
User avatar

Joined: 12th Apr, 2008
Posts: 17757
Location: Oxford
Sir Taxalot wrote:
Since learning to use pivot tables and the 'grouping' ability within pivot tables, I have felt like the excel boss at work. You should seriously learn to use pivot tables, that will put you ahead of easily 90% of people that list 'MS Office' on their CV.


Very much this. People look on in awe when I use pivots, and I quietly think back to how when I was first shown them, I too thought they were witchcraft. One day I'll level up and master INDEX MATCH MATCH.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jun 23, 2017 11:06 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 4296
Not specifically Excel help, but related.

I am creating a Mail Merge in Word (people still do those, right?)

Each mail will have a unique link for users to click. This is being pulled from an Excel document.

Question: How do I merge that link under display text i.e. I can pull in the link using Mail Merge, but cannot work out how to change the display text e.g. click here.

At present it's just merging the url as is.

The person who helps will be the proud recipient of a mail merged message from yours truly.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jun 23, 2017 11:10 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
This StackOverflow post might help?
https://stackoverflow.com/questions/156 ... -same-text

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jun 23, 2017 11:10 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 12034
Location: Sunny Stoke
What happens if you do the 'click here' type stuff in Excel first? i.e., using =HYPERLINK(A1,"Click Here") where A1 holds your URL and then bringing that new field through instead?

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jun 23, 2017 12:47 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 4296
Thanks folks. Excel isn't accepting either of these and I can't work out why:

=hyperlink(http://www.gmail.com,)
=hyperlink(http://www.gmail.com,[Click_Here])

I hate Excel.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jun 23, 2017 12:51 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 12034
Location: Sunny Stoke
The URL and display text will need quotes around them.

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jun 23, 2017 12:52 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 4296
devilman wrote:
The URL and display text will need quotes around them.


boom! Thank you


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jun 23, 2017 12:55 
User avatar
EvilTrousers

Joined: 30th Mar, 2008
Posts: 3073
If you stick the URL in one cell, the text in another (exactly the same format as above) then do =hyperlink(A1,B1) it works on mine but actually typing it in doesn't.

Stupid Excel

_________________
Everyone but Zardoz is better than me at videogames.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jun 23, 2017 13:13 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 4296
Trousers wrote:
If you stick the URL in one cell, the text in another (exactly the same format as above) then do =hyperlink(A1,B1) it works on mine but actually typing it in doesn't.

Stupid Excel


That's even better! Thank you.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 13:43 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
Okay, need help. Or rather elegance.
I have two time variables as columns, eg:
start time: - 14:30
end time: - 17:30

and another set of times and costs:
Code:
08:00   16:59   £83.00
17:00   21:59:59   £95.00
22:00   07:59:59   £23.00
22:00   07:59:59   £105.00


I need to assign the difference between start and end times pro rata against the relevant cost level, ie

17:30 - 14:30 = 3 hours.
2.5 hours @ 83
0.5 @ 95

No, I could spend ages doing this through various if statements, but I wondered if there were a more elegant way.

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 14:06 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
I would get the number of minutes between the two times:
Code:
=(B1-A1)*1440

And then multiply by the cost per minute:
Code:
=((B1-A1)*1440)*(C1/60)


If you need to round up or down to hours then just divide by 60 rather than 1440 and use =roundup() or =rounddown(). Then leave C1 as it is.

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 14:08 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
but I need a way of allocating the minutes, or am I missing something?

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 15:39 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
This page looks to be trying to solve the same sort of problem with the banding of pay rates and calculating how much of each rate falls into each band:

http://www.meadinkent.co.uk/xl_payenh.htm

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 15:52 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
the problem I am having is that the shifts can start at any point and span multiple cost boundaries.

thanks though.

I may have to give up.

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 16:22 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
I've got to say, I'm not really sure what you're trying to do :S

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 16:27 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Are there dates, too? Some of those times have a later start then end, suggesting they've rolled over midnight

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 16:44 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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.

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 18:16 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Except it could be 8 days later, there's no way to tell

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 19:22 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Valid.

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 19:34 
SupaMod
User avatar
Commander-in-Cheese

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

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 01, 2017 21:03 
User avatar
Ticket to Ride World Champion

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

_________________
No, it was a giant robot castle!


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

Joined: 31st Mar, 2008
Posts: 8647
Why are there two rows for 22:00 to 7:59:59?


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 8:44 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
We'll, that's the next step, that is the weekend rate! Ignore that for now.

_________________
No, it was a giant robot castle!


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

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


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

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


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

Joined: 30th Mar, 2008
Posts: 49232
It looks amazing though, Joans

_________________
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 13:33 
SupaMod
User avatar
Commander-in-Cheese

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

_________________
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 13:36 
User avatar

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


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

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

_________________
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 13:45 
User avatar

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


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

Joined: 31st Mar, 2008
Posts: 6496
And people say Perl is unreadable.


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

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


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

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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 14:51 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
Wow! Joans, am of work today, week try it tomorrow, but seriously, thanks for the effort!

_________________
No, it was a giant robot castle!


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

Joined: 31st Mar, 2008
Posts: 8647
It still doesn't quite work, sorry, something like 21:00 - 09:00 sends it a bit funny.


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

All times are UTC [ DST ]


Who is online

Users browsing this forum: Columbo, Kern 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.