User Tag List

Page 1 of 3 123 LastLast
Results 1 to 50 of 107

Thread: Excel Quiz Of The Day

  1. #1
    Senior Member
    Join Date
    Sep 2015
    Posts
    172
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Excel Quiz Of The Day

    Thought I'd start this one back up as I have an Excel related question.

    Toggle Spoiler


    Hope that makes sense. If needed I can send the spreadsheet over.

    Going a step further I'm wondering if there's a way I can enter predicted use and then have Excel work out the most efficient cutting lists based on usage / scrap. I imagine that's fairly complex / probably not what excel is for (?)

    Thanks guys

  2. #2
    Just Luca, but still a DJ Luca's Avatar
    Join Date
    Sep 2015
    Location
    Toronto
    Posts
    1,530
    Mentioned
    27 Post(s)
    Tagged
    0 Thread(s)
    If you can send the spreadsheet over, I'd take a look.

    Excel can indeed do that; you'd be using a function called Solver, which uses the Simplex algorithm to solve Linear Programming problems like this (minimize/maximize variables given constraints).

  3. #3
    More successful than most Magic's Avatar
    Join Date
    Sep 2015
    Location
    Scotchland
    Posts
    17,921
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    How do I concatenate or &:

    A1+B1 and then add @companyname.com

    to make a username? Getting on my fucking tits and the examples are useless.

  4. #4
    More successful than most Magic's Avatar
    Join Date
    Sep 2015
    Location
    Scotchland
    Posts
    17,921
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, just made a text field with the @companyname.com and added that in to concatenate.

  5. #5
    ram it up your shitpipe Giggles's Avatar
    Join Date
    Sep 2015
    Location
    Kildare
    Posts
    30,459
    Mentioned
    138 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Magic View Post
    How do I concatenate or &:

    A1+B1 and then add @companyname.com

    to make a username? Getting on my fucking tits and the examples are useless.
    Just make a text field with the @companyname.com and add that in to concatenate.

  6. #6
    More successful than most Magic's Avatar
    Join Date
    Sep 2015
    Location
    Scotchland
    Posts
    17,921
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)

  7. #7
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,309
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    Put @whatever.com in cell C1 (say) and then:

    =CONCATENATE(A1,B1,$C$1)

  8. #8
    More successful than most Magic's Avatar
    Join Date
    Sep 2015
    Location
    Scotchland
    Posts
    17,921
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Can I add a prefix to a bigass bunch of numbers?

    So C1,C2,C3 etc has 221,222,223

    Can I edit en-masse to make them 2221,2222,2223?

  9. #9
    More successful than most Magic's Avatar
    Join Date
    Sep 2015
    Location
    Scotchland
    Posts
    17,921
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Format cells, custom, type = 2000.


  10. #10
    More successful than most Magic's Avatar
    Join Date
    Sep 2015
    Location
    Scotchland
    Posts
    17,921
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Oh for fuck's sake all the first names have white space at the end! Can I remove this en-masse?!

  11. #11
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,309
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    =TRIM. Only works on later versions though.

  12. #12
    More successful than most Magic's Avatar
    Join Date
    Sep 2015
    Location
    Scotchland
    Posts
    17,921
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    I've got 2016 so it worked a treat. Thanks. Just copied and pasted the TRIM'd values.

  13. #13
    Senior Member
    Join Date
    Sep 2015
    Location
    Jacksonville, FL
    Posts
    8,567
    Mentioned
    51 Post(s)
    Tagged
    0 Thread(s)
    =trim(Cell that needs trimming

    Also =A1&B1&"@Company.com" would have worked for the the email

  14. #14
    Senior Member Bernanke's Avatar
    Join Date
    Sep 2015
    Posts
    2,471
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    So, I have a very basic question that I really need to figure out since I'm trying to wean off using the mouse whatsoever when using Excel.

    When I'm editing a function, how do I through the keyboard go from jumping around in different parts of it to actually selecting cells again? Say I create =SUM(, then I can use my arrow keys to find the range I want. If I then end up with =SUM(F8:F12) and leave the function, go back to it and open it for editing via F2, I can only use the arrow keys to move about in the function. How do I for example select F12 to navigate to new cells, instead of typing what I need?

  15. #15
    Senior Member Adamski's Avatar
    Join Date
    Sep 2015
    Posts
    2,646
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Don't think it's possible, only way I can think is to create a series of named ranges and you would be able to switch between them if you wanted, but you wouldn't just be able to select a random amount of cells.

  16. #16
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,309
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    If you're typing a formula, then the formula is king.

  17. #17
    Senior Member Bernanke's Avatar
    Join Date
    Sep 2015
    Posts
    2,471
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Fair enough. Makes sense I guess, but since it gives you the option of choosing cells freely when you first type the formula I assumed there was a way to get back to that "state".

  18. #18
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,309
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    There is, by starting again.

  19. #19
    Senior Member
    Join Date
    Sep 2015
    Posts
    7,747
    Mentioned
    37 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Bernanke View Post
    So, I have a very basic question that I really need to figure out since I'm trying to wean off using the mouse whatsoever when using Excel.

    When I'm editing a function, how do I through the keyboard go from jumping around in different parts of it to actually selecting cells again? Say I create =SUM(, then I can use my arrow keys to find the range I want. If I then end up with =SUM(F8:F12) and leave the function, go back to it and open it for editing via F2, I can only use the arrow keys to move about in the function. How do I for example select F12 to navigate to new cells, instead of typing what I need?
    There is a way to do this, but it isn't very nice.

    Press F2 to enter the cell, the use shirt+arrows keys to select(highlight) the reference to want replacing, once highlighted, press F5 to open the Go To box, enter the new reference and hit enter.
    Urgh.

  20. #20
    Senior Member Adamski's Avatar
    Join Date
    Sep 2015
    Posts
    2,646
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Ah that's not bad actually, nice one.

  21. #21
    Senior Member Bernanke's Avatar
    Join Date
    Sep 2015
    Posts
    2,471
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Damn, that actually does it! It might not be the cleanest way, but it's nice to learn the option at least.

  22. #22
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,022
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    Can someone help me with a really simple spreadsheet to calculate daily bonus please?

    https://files.fm/down.php?i=55c3ecxc&n=bonus.ods

    On the first sheet 'rates' there is a list of what each amount earns in bonus:
    Less than £1200 = no bonus
    £1200 = £5
    £1300 = £10
    £1400 = £15
    £1500 = £20
    £2000+ = £40

    The second sheet 'april' is a sheet where the user can enter their daily takings and it will show how much bonus they're entitled too. The user will not enter anything if they do not hit the £1200 threshold, hence the gaps in the dates.

    There will then be a new sheet every month from now until the end of time, 'may' is already on there but obviously not complete.

    If someone could either edit it and reupload, or tell me what to do, I'd be very appreciative. Obviously it's a piece of piss to work out manually but I'd prefer to be able to keep it all tracked in one place and it'd speed things up in the long run if it was calculated automatically, because some people are thick.

    Thanks TTH.
    I'm a twit

  23. #23
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    In C3:

    =IFS(B3<1199.99, 0, AND(B3 >1200, B3<1299.99), 5, AND(B3 >1300, B3<1399.99), 10, AND(B3 >1400, B3<1499.99), 15, AND(B3 >1500, B3<1999.99), 20, B3 >2000, 40)

    And drag to fill the columns below for the rest of the dates.

    I'd be interested to hear if anyone has a more elegant way of doing it.

  24. #24
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    On my phone at the moment, but you could try rounddown and vlookup.

  25. #25
    Senior Member
    Join Date
    Sep 2015
    Posts
    7,747
    Mentioned
    37 Post(s)
    Tagged
    0 Thread(s)
    Dino is correct. However, I would rename the bonus payments and limits (by just entering the name into the Name Box), and then use the names in the formula, therefore, you can change the limit and rates at a future date and not have to go back and edit the formula;





  26. #26
    Junior Member
    Join Date
    May 2017
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use the following formula in cell C3 and drag it down:

    =MAX(IF(B3>Rates!$A$1:$A$5,Rates!$B$1:$B$5))

    This is an array formula, which means you have to press ctrl + shift + enter once you've written it. Once that's done you'll get {} wrapping the formula.

  27. #27
    Senior Member
    Join Date
    Sep 2015
    Posts
    7,747
    Mentioned
    37 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by el_rhysio View Post
    You can use the following formula in cell C3 and drag it down:

    =MAX(IF(B8>Rates!$A$1:$A$5,Rates!$B$1:$B$5))

    This is an array formula, which means you have to press ctrl + shift + enter once you've written it. Once that's done you'll get {} wrapping the formula.
    That's much more elegant. Nice work (assuming it works!).

  28. #28
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,022
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    Struggling here. Presumably because Open Office Calc is rubbish. I tried changing commas to semi-colons but that just changes the error from Error508 to #NAME? Google suggested checking my locale settings are UK but they all seem correct.

    Could someone please upload a working version? Honestly not too fussy about fancy presentation, but the ability to change the amounts and it automatically update sounds good.
    I'm a twit

  29. #29
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Not sure why you can't just use a simple vlookup? Here you go.

    https://drive.google.com/open?id=0B2...2Z2YkQtZFFOYUU

  30. #30
    Senior Member
    Join Date
    Sep 2015
    Posts
    7,747
    Mentioned
    37 Post(s)
    Tagged
    0 Thread(s)

  31. #31
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    This has been doing my fucking head in.

    I want to reference values from different sheets

    The names of all of my sheets are in row 1 on each sheet.

    I'm using a formula like this:

    =INDIRECT(""&B$1&"!B4")

    A picture (I want the value in cell B4 from Sheet2): http://imgur.com/a/q7mhP

    It works, but I have hundreds of these to do, so I obviously want to autofill. When I do that, the cell value doesn't update properly. It should be C4 at the end, not B4 : http://imgur.com/a/EU4xj

    Is there a better way of doing this?

  32. #32
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Use a combination of =INDIRECT and =ROW().

    =INDIRECT(B1&"!b"&ROW())

    Obviously put this formula in row 4 if all the data you want is in row 4.

  33. #33
    Senior Member Weaver's Avatar
    Join Date
    Sep 2015
    Posts
    901
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Having a stinker with Excel today, so I’m looking for some pointers if anyone can give a hand.

    I’m looking to insert a control into a spreadsheet which will organise a list of information by order of date.

    I’ve got another spreadsheet with that function but the formulas were created by someone else/hidden, so I can’t just nick them unfortunately.

    That one has an arrow and gives a choice of ascending or descending.

  34. #34
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Weaver View Post
    Having a stinker with Excel today, so I’m looking for some pointers if anyone can give a hand.

    I’m looking to insert a control into a spreadsheet which will organise a list of information by order of date.

    I’ve got another spreadsheet with that function but the formulas were created by someone else/hidden, so I can’t just nick them unfortunately.

    That one has an arrow and gives a choice of ascending or descending.
    Do you mean a table? Select all of the information you want sorted (all columns/rows, including headers) -> Insert tab at the top -> Table

    You should then be able to click the headers and sort ascending/descending

  35. #35
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hfswjyr View Post
    Use a combination of =INDIRECT and =ROW().

    =INDIRECT(B1&"!b"&ROW())

    Obviously put this formula in row 4 if all the data you want is in row 4.
    Sorry I never got back to you. This kind of worked, but I ended up with a clusterfuck of recursive formulas so I had to completely change approach.

  36. #36
    Senior Member Bernanke's Avatar
    Join Date
    Sep 2015
    Posts
    2,471
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    I have a bunch of cells referencing a specific time, but the formatting for many of them ended up wrong, and I need help transforming the latter to the former.

    2017-09-03 15:01:00
    20170904095100

    So I basically need to break it up into date and time. Any ideas?

    Edit: Solved it with some RIGHT and LEFT formulas.

  37. #37
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,309
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    Concatenate is your friend.

  38. #38
    DEATH TO THE WEIRD Raoul Duke's Avatar
    Join Date
    Sep 2015
    Location
    Haarlem
    Posts
    6,802
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)
    Data -> text to columns should do it as well. Top, top Excel function that

  39. #39
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    Text to columns is one of the finest things in life, but I do sometimes wish you could be more specific with it. Say, split when you get to the first number or something.

  40. #40
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    Mind blowing revelation of the week:

    You can create named ranged by selecting your range and entering the name into the box on the extreme left of the formula bar.

    That's literally minutes of labour shaved off every week for me.

  41. #41
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,022
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    Anyone fancy making this spreadsheet functional?

    The Reid's mate reckons you'd be better off betting on Man Of The Match rather than hat-trick (I'll be doing the latter) so want to test which would be more lucrative come the end of the season. All bets are £1.

    I know decimal odds would make more sense for spreadsheet purposes but everyone who will be looking at it uses fractional, hence the longwinded methods.

    Is there a way to check the Yes/No boxes depending on the outcome, that would then either set the Winnings cell to 0, or the actual winnings (£6.50).

    Also would be good to have a TOTAL box somewhere, and maybe red/green depending on the outcome.

    Cheers.
    I'm a twit

  42. #42
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,309
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    You can convert fractional into decimal with the following (say, 11/2, 11 is A1 and 2 is B1):

    =(A1/B1)+1

    Then just multiply the stake by that to produce winning returns.

  43. #43
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Baz View Post
    Is there a way to check the Yes/No boxes depending on the outcome, that would then either set the Winnings cell to 0, or the actual winnings (£6.50).

    Also would be good to have a TOTAL box somewhere, and maybe red/green depending on the outcome.
    1. Use a simple IF statement
    2. SUM for total and use conditional formatting

  44. #44
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    I've got this MATCH formula that uses arrays:

    =MATCH(1, INDIRECT("'"&$V$10&"'!C2:AZ2")=U26*INDIRECT("'"&$V $10&"'!C3:AZ3")=$V$11, 0))

    So basically return the index where both of these return True:

    INDIRECT("'"&$V$10&"'!C2:AZ2")=U26
    and
    INDIRECT("'"&$V$10&"'!C3:AZ3")=$V$1

    In this case that is 4, and they both return that when calculated individually, but the multiplication returns an array of #VALUE!s

    Anyone know why?

  45. #45
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    =MATCH($U26, iF(INDIRECT("'"&$V$10&"'!C3:AZ3")=$V$11, INDIRECT("'"&$V$10&"'!C2:AZ2")), 0)


  46. #46
    Senior Member Dan's Avatar
    Join Date
    Sep 2015
    Posts
    5,514
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    Is there a formula that will multiply a number by how far it is outside of a particular range? So for instance, (A1+B1+C1)*(D1/100), where both 97 & 103 would return 0.97, and instead of 100, it's a range such as 80-120?

    Ideally in that example, both 72 & 132 would return 0.9, since they're both 10% outside of the range on either end. I'm using numbers, but it seems to be basically the same as excel.

  47. #47
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    Can you explain it differently? I don't think I understand. What are A1, B1, C1, D1?

  48. #48
    ram it up your shitpipe Giggles's Avatar
    Join Date
    Sep 2015
    Location
    Kildare
    Posts
    30,459
    Mentioned
    138 Post(s)
    Tagged
    0 Thread(s)
    Yeah I've no idea what you're asking either.

  49. #49
    Senior Member Dan's Avatar
    Join Date
    Sep 2015
    Posts
    5,514
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    A, B & C are basically irrelevant to the problem, it’s the other half of it that’s the issue.

    Say for instance, the ABC side is a total test score, then I want to multiply that by how close they are to a particular weight (in the example above, their weight would be D1 & the ideal weight would be the 100).

    If the ideal weight is between 90 & 110 I want to be able to have every weight within that range be multiplied by 1, and every weight outside that range be multiplied by less & less the further away from that range they are.

    For instance, if the ABC side equals 10, and their weight is between 90 & 110, the formula above should basically read (10)*(1), but if their weight was 81 it should read (10)*(0.9) since they’re 10% outside the ideal range. Equally a weight of 121 should provide the same result.

    To be honest I’m not sure I’m explaining it very well & I think it’s probably easier to just have to do a bit more work & do it manually

  50. #50
    Senior Member
    Join Date
    Sep 2015
    Posts
    3,865
    Mentioned
    49 Post(s)
    Tagged
    0 Thread(s)
    You could make that in a very basic way, by using the difference from the target as a fraction and then a condition/lookup table surely.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •