Be Excellent To Each Other https://www.beexcellenttoeachother.com/forum/ |
|
Excel help https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=9988 |
Page 1 of 3 |
Author: | TheVision [ Mon Mar 10, 2014 11:04 ] |
Post subject: | Excel help |
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? |
Author: | zaphod79 [ Mon Mar 10, 2014 11:08 ] |
Post subject: | Re: Excel help |
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 |
Author: | zaphod79 [ Mon Mar 10, 2014 11:14 ] |
Post subject: | Re: Excel help |
So your data ends up looking like this Attachment: data.png And the pivot gives you Attachment: pivot.png
|
Author: | TheVision [ Mon Mar 10, 2014 11:58 ] |
Post subject: | Re: Excel help |
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. |
Author: | zaphod79 [ Mon Mar 10, 2014 12:04 ] |
Post subject: | Re: Excel help |
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' ? |
Author: | TheVision [ Mon Mar 10, 2014 12:06 ] |
Post subject: | Re: Excel help |
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. |
Author: | zaphod79 [ Mon Mar 10, 2014 12:11 ] |
Post subject: | Re: Excel help |
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 |
Author: | Trooper [ Mon Mar 10, 2014 14:36 ] |
Post subject: | Re: Excel help |
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. |
Author: | TheVision [ Mon Mar 10, 2014 15:23 ] |
Post subject: | Re: Excel help |
Bingo! That's done it. Thanks both for your help. |
Author: | MaliA [ Wed Apr 12, 2017 15:50 ] |
Post subject: | Re: Excel help |
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? |
Author: | devilman [ Wed Apr 12, 2017 15:54 ] |
Post subject: | Re: Excel help |
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. |
Author: | Dr Zoidberg [ Wed Apr 12, 2017 21:31 ] |
Post subject: | Re: Excel help |
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. |
Author: | Sir Taxalot [ Wed Apr 12, 2017 23:35 ] |
Post subject: | Re: Excel help |
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. |
Author: | Grim... [ Wed Apr 12, 2017 23:36 ] |
Post subject: | Re: Excel help |
You appear to be answering a three year old question. |
Author: | Sir Taxalot [ Thu Apr 13, 2017 2:10 ] |
Post subject: | Re: Excel help |
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 |
Author: | Kern [ Thu Apr 13, 2017 8:59 ] |
Post subject: | Re: Excel help |
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. |
Author: | Findus Fop [ Fri Jun 23, 2017 11:06 ] |
Post subject: | Re: Excel help |
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. |
Author: | Mr Russell [ Fri Jun 23, 2017 11:10 ] |
Post subject: | Re: Excel help |
This StackOverflow post might help? https://stackoverflow.com/questions/156 ... -same-text |
Author: | devilman [ Fri Jun 23, 2017 11:10 ] |
Post subject: | Re: Excel help |
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? |
Author: | Findus Fop [ Fri Jun 23, 2017 12:47 ] |
Post subject: | Re: Excel help |
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. |
Author: | devilman [ Fri Jun 23, 2017 12:51 ] |
Post subject: | Re: Excel help |
The URL and display text will need quotes around them. |
Author: | Findus Fop [ Fri Jun 23, 2017 12:52 ] |
Post subject: | Re: Excel help |
devilman wrote: The URL and display text will need quotes around them. boom! Thank you |
Author: | Trousers [ Fri Jun 23, 2017 12:55 ] |
Post subject: | Re: Excel help |
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 |
Author: | Findus Fop [ Fri Jun 23, 2017 13:13 ] |
Post subject: | Re: Excel help |
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. |
Author: | Bobbyaro [ Tue Aug 01, 2017 13:43 ] |
Post subject: | Re: Excel help |
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. |
Author: | Grim... [ Tue Aug 01, 2017 14:06 ] |
Post subject: | Re: Excel help |
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. |
Author: | Bobbyaro [ Tue Aug 01, 2017 14:08 ] |
Post subject: | Re: Excel help |
but I need a way of allocating the minutes, or am I missing something? |
Author: | Mr Russell [ Tue Aug 01, 2017 15:39 ] |
Post subject: | Re: Excel help |
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 |
Author: | Bobbyaro [ Tue Aug 01, 2017 15:52 ] |
Post subject: | Re: Excel help |
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. |
Author: | Grim... [ Tue Aug 01, 2017 16:22 ] |
Post subject: | Re: Excel help |
I've got to say, I'm not really sure what you're trying to do |
Author: | Cras [ Tue Aug 01, 2017 16:27 ] |
Post subject: | Re: Excel help |
Are there dates, too? Some of those times have a later start then end, suggesting they've rolled over midnight |
Author: | Grim... [ Tue Aug 01, 2017 16:44 ] |
Post subject: | Re: Excel help |
Oh, that's not such a hardship. Just do an if to see if one is bigger than the other and take the evening start hour away from the morning end hour plus 24. |
Author: | Cras [ Tue Aug 01, 2017 18:16 ] |
Post subject: | Re: Excel help |
Except it could be 8 days later, there's no way to tell |
Author: | Grim... [ Tue Aug 01, 2017 19:22 ] |
Post subject: | Re: Excel help |
Valid. |
Author: | Cras [ Tue Aug 01, 2017 19:34 ] |
Post subject: | Re: Excel help |
I've just actually realised what Bobby is fuckering on about. Those time slots aren't the start and end times, they're rates. So if you work three hours between 7pm and 7am, the rate is X. I'll have another look when all the Zeds have been dealt with. |
Author: | Bobbyaro [ Tue Aug 01, 2017 21:03 ] |
Post subject: | Re: Excel help |
Yes, there are three time slots, in the list, these assign a cost/hour to any work done. The work done can span these time slots and needs to be divided accordingly. So if I start work at 16:50 and finish at 17:20 I have 10 minutes at rate 1 and 20 at rate 2. It is unlikely they would span more than 3 shifts, though. |
Author: | Joans [ Wed Aug 02, 2017 6:55 ] |
Post subject: | Re: Excel help |
Why are there two rows for 22:00 to 7:59:59? |
Author: | Bobbyaro [ Wed Aug 02, 2017 8:44 ] |
Post subject: | Re: Excel help |
We'll, that's the next step, that is the weekend rate! Ignore that for now. |
Author: | Joans [ Wed Aug 02, 2017 13:20 ] |
Post subject: | Re: Excel help |
Elegant this is not. Assuming the stuff you posted on the previous page is in cells ("A1:C3"), put your start and end times in D1 and E1, then put this monstrosity into E1 and copy down for each shift range (i.e. Rows 1-3). This should give you the total rate for each range (I assumed it was an hourly rate, and rounded to the nearest 15 minutes). =(ROUND(MINUTE(IF((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))<0, ((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))+1)-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1))), (IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))))/15, 0)/4*C1)+(HOUR(IF((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))<0, ((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))+1)-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1))), (IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))))*C1) |
Author: | Joans [ Wed Aug 02, 2017 13:24 ] |
Post subject: | Re: Excel help |
Actually, ignore that, it doesn't work if you span multiple shifts and days, 14.30 - 17.30 works, 23.00 - 5.00 works, but 21.00 - 05.00 doesn't. Does this have to be done in Excel? |
Author: | Cras [ Wed Aug 02, 2017 13:29 ] |
Post subject: | Re: Excel help |
It looks amazing though, Joans |
Author: | Cras [ Wed Aug 02, 2017 13:33 ] |
Post subject: | Re: Excel help |
I'd be thinking something other than excel would be preferable for this. Or VBA sitting behind excel would certainly do it. I think maybe one approach would be to split the time you worked into 30-minute chunks, and for each chunk check which bracket it's in and apply (half) the appropriate rate. |
Author: | Bluecup [ Wed Aug 02, 2017 13:36 ] |
Post subject: | Re: Excel help |
Would this work? Create a static table, start and end times along x and y, and fill in the calculated values. Then use index() and match () to look up from the table. |
Author: | Cras [ Wed Aug 02, 2017 13:42 ] |
Post subject: | Re: Excel help |
Wouldn't you spend about the next 15 years working out what all the calculated values would need to be to fill in the table? |
Author: | Joans [ Wed Aug 02, 2017 13:45 ] |
Post subject: | Re: Excel help |
Yeah even VBA would be helpful, however I work with what I've got. So, try this... =(ROUND(MINUTE((IF((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1))))<0, ((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))+1)-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))), (IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))))))/15, 0)/4*C1)+(HOUR((IF((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1))))<0, ((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))+1)-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))), (IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))))))*C1) |
Author: | Squirt [ Wed Aug 02, 2017 13:46 ] |
Post subject: | Re: Excel help |
And people say Perl is unreadable. |
Author: | Bluecup [ Wed Aug 02, 2017 13:48 ] |
Post subject: | Re: Excel help |
Cras wrote: Wouldn't you spend about the next 15 years working out what all the calculated values would need to be to fill in the table? I'm sure a formula could be worked out to do most of it. |
Author: | Joans [ Wed Aug 02, 2017 13:53 ] |
Post subject: | Re: Excel help |
Squirt wrote: And people say Perl is unreadable. In fairness it's because I had about 7 calculated fields, which I combined into one user-friendly formula. If I did it all separately it wouldn't be that bad, but where's the fun in that? |
Author: | Bobbyaro [ Wed Aug 02, 2017 14:51 ] |
Post subject: | Re: Excel help |
Wow! Joans, am of work today, week try it tomorrow, but seriously, thanks for the effort! |
Author: | Joans [ Wed Aug 02, 2017 15:12 ] |
Post subject: | Re: Excel help |
It still doesn't quite work, sorry, something like 21:00 - 09:00 sends it a bit funny. |
Page 1 of 3 | All times are UTC [ DST ] |
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group http://www.phpbb.com/ |