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
: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
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 :S
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/