Be Excellent To Each Other

And, you know, party on. Dude.
It is currently Wed Aug 16, 2017 14:34

All times are UTC [ DST ]




Reply to topic  [ 75 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: 13358
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: 13009
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

_________________
Image


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: 13009
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.

_________________
Image


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

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

_________________
Image


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

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

_________________
Image


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: 18887
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.

_________________
http://www.trooperlooper.co.uk - Awesome MX5 blog of awesomeness.
http://www.anamateursews.co.uk - Look at me, i'm a sewing machine.


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

Joined: 30th Mar, 2008
Posts: 13358
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: 41844
Location: West Yorkshire
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: 9569
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: 2979
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: 3019
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.

_________________
Image


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

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

_________________
Image


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

Joined: 12th Apr, 2008
Posts: 11902
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: 1366
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: 11416
This StackOverflow post might help?
https://stackoverflow.com/questions/156 ... -same-text

_________________
Always proof read carefully in case you any words out

Do some price comparison on games, DVDs, books or CDs at Find It Cheapest.com
Image


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

Joined: 30th Mar, 2008
Posts: 9569
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: 1366
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: 9569
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: 1366
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: 2876
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

_________________
"Trains are part of our business infrastructure. By photographing them, you're giving Intel to sworn enemies of our nation"


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

Joined: 14th May, 2009
Posts: 1366
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: 11390
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: 61882
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: 11390
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: 11416
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

Do some price comparison on games, DVDs, books or CDs at Find It Cheapest.com
Image


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: 11390
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: 61882
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  
 
Display posts from previous:  Sort by  
Reply to topic  [ 75 posts ]  Go to page 1, 2, 3  Next

All times are UTC [ DST ]


Who is online

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

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