User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 51 to 100 of 107

Thread: Excel Quiz Of The Day

  1. #51
    Senior Member
    Join Date
    Sep 2015
    Posts
    3,865
    Mentioned
    49 Post(s)
    Tagged
    0 Thread(s)
    Actually, even multiple if statements would do it.

  2. #52
    Senior Member Dan's Avatar
    Join Date
    Sep 2015
    Posts
    5,514
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    I don’t know what any of those things are

    The first things, I can figure out if statements. That would probably do the job, actually.

  3. #53
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    https://docs.google.com/spreadsheets...it?usp=sharing

    Is this what you mean?

    Not 100% sure about your explanation of the problem. Maybe try using real terms.
    Last edited by hfswjyr; 08-05-2018 at 08:55 AM.

  4. #54
    Senior Member Dan's Avatar
    Join Date
    Sep 2015
    Posts
    5,514
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    It’s alright, if statements did the job.

  5. #55
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,019
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    I have a spreadsheet where if a cell (C141) contains any text AND the one beside it (D141) is less than 15, I need the next cell (E141) to display 15. However if D141 is greater than 15 then E141 needs to be blank. However if C141 is blank, I want D141 and E141 to remain blank (as I've not input anything into C141 yet). Basically its for paying a claim where each claim is worth £1.50. C141 is the number of claims and D141 is the amount to be paid, however they get at least £15 so long as they do at least one claim. Does that make sense? So if C141 is 4, D141 is only £6.00, but they'll still get paid £15. And if C141 is 240 they get paid £360 and therefore the minimum payment amount doesn't come into play. So E141 will always be either 0 or 15. Then F141 is the combined total of D141 + E141, which will always be at least. If anyone can tell me what to put in E141 to reflect this I'd be grateful.
    I'm a twit

  6. #56
    Senior Member
    Join Date
    Sep 2015
    Posts
    7,745
    Mentioned
    37 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Baz View Post
    I have a spreadsheet where if a cell (C141) contains any text AND the one beside it (D141) is less than 15, I need the next cell (E141) to display 15. However if D141 is greater than 15 then E141 needs to be blank. However if C141 is blank, I want D141 and E141 to remain blank (as I've not input anything into C141 yet). Basically its for paying a claim where each claim is worth £1.50. C141 is the number of claims and D141 is the amount to be paid, however they get at least £15 so long as they do at least one claim. Does that make sense? So if C141 is 4, D141 is only £6.00, but they'll still get paid £15. And if C141 is 240 they get paid £360 and therefore the minimum payment amount doesn't come into play. So E141 will always be either 0 or 15. Then F141 is the combined total of D141 + E141, which will always be at least. If anyone can tell me what to put in E141 to reflect this I'd be grateful.
    =IF(C141<>"",IF(D141<15,15,""),"")
    Perhaps

  7. #57
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    I had to read that confusing explanation about 50 times, but I think I've finally understood what you mean.

    =IF(C141<>"",IF(D141<15,15,D141),"")
    Don't use column F.

    Or use JA's solution and column F is =max(D141,E141)

  8. #58
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,019
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    Cheers boys.

    It’s an old spreadsheet so column F is required so will use the combination.
    I'm a twit

  9. #59
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,019
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    I’m here again.

    Exported data lists the data (and a report number) as follows:

    20170202-00070-1
    20170217-00257-1
    etc.

    I want to use format cells>custom so that it changes the display of this to something like
    02/02/2017 (00070-1)
    17/02/2017 (00257-1)

    Is that possible?
    I'm a twit

  10. #60
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,308
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    You could re-route it using CONCATENATE, chopping it up and then reassembling in the new format. Don't know if it can get that sort of number into a date format in the custom format bit but it can do most things so it wouldn't surprise me.

  11. #61
    Senior Member Boydy's Avatar
    Join Date
    Sep 2015
    Posts
    12,592
    Mentioned
    78 Post(s)
    Tagged
    0 Thread(s)

  12. #62
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    A1=20170202-00070-1
    b1=concatenate(mid($a1,7,2),"/",mid($a1,5,2),"/",mid($a1,1,4)," (",mid($a1,10,7),")")
    a2=20170217-00257-1
    b2=concatenate(mid($a2,7,2),"/",mid($a2,5,2),"/",mid($a2,1,4)," (",mid($a2,10,7),")")

  13. #63
    Custom User Title phonics's Avatar
    Join Date
    Aug 2015
    Posts
    18,152
    Mentioned
    118 Post(s)
    Tagged
    0 Thread(s)
    I manage to code enough to make my own websites and put this place together but every time I come into this thread it's absolute gobbledygook.

  14. #64
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Boydy View Post

    I've been putting in an hour or two a day of Python for a few months now. I started off with this and I'd definitely recommend it to anyone.

    I'm struggling to get past this level though. I think there's a limit to how far you can get just through googling stuff you need to know how to do.

  15. #65
    Custom User Title phonics's Avatar
    Join Date
    Aug 2015
    Posts
    18,152
    Mentioned
    118 Post(s)
    Tagged
    0 Thread(s)
    I've been googling how to do my job for 10 years now.

  16. #66
    Senior Member Boydy's Avatar
    Join Date
    Sep 2015
    Posts
    12,592
    Mentioned
    78 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dino View Post
    I've been putting in an hour or two a day of Python for a few months now. I started off with this and I'd definitely recommend it to anyone.

    I'm struggling to get past this level though. I think there's a limit to how far you can get just through googling stuff you need to know how to do.
    I don't really have any python experience but I work with Java now. Pm me if you want any help.

  17. #67
    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)
    Is that website the best way for a complete beginner to get into python?

  18. #68
    Senior Member Boydy's Avatar
    Join Date
    Sep 2015
    Posts
    12,592
    Mentioned
    78 Post(s)
    Tagged
    0 Thread(s)
    Yeah, probably.

  19. #69
    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)
    Must have a better look through it.

  20. #70
    Senior Member
    Join Date
    Feb 2018
    Posts
    422
    Mentioned
    12 Post(s)
    Tagged
    0 Thread(s)
    I have 100 points on a chart which require numerical values. If I am able to work out the value for every 15th number, would I be able to use a formula to determine the other values?

    It is based on the values creating a tangential curve.

    0= 0
    1=
    2=
    3=
    4=
    .
    .
    .
    .
    15= 11.75
    16=
    17
    18
    19=
    20=
    21=
    22=
    23=
    24=
    25=
    26=
    27=
    28=
    29=
    30= 27.3

  21. #71
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,308
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    You'll need to call Dr Aussie for that one.

  22. #72
    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)
    You could probably do it by some kind of (11.75 / COUNTA $A$1:A14).

    I'm too tired to figure out the detail.

  23. #73
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Are you just trying to interpolate?

    No idea what you mean by tangential curve. Tangential to what?

  24. #74
    Senior Member
    Join Date
    Feb 2018
    Posts
    422
    Mentioned
    12 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hfswjyr View Post
    Are you just trying to interpolate?

    No idea what you mean by tangential curve. Tangential to what?
    So if you drew a curve from point to point it would be tangential.

    I have been given a spreadsheet which someone has created and each number (0 > 90) has a value against it. However, only the values for the following numbers were entered manually (0, 15, 30, 45, 60, 75, 90). The rest have somehow been worked out and I am trying to work out how (no formulas are in the spreadsheet).

    An example of how the spreadsheet looks - https://imgur.com/a/D1xSvIq

  25. #75
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    So you are trying to find the formula to relate X and Y?

    Plot the data on a scattergraph in Excel, then:
    https://www.excel-easy.com/examples/trendline.html

  26. #76
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,019
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    I’ve got a problem related to rounding money up/down.

    I have an invoice amount that I enter into a cell and it needs to be split three ways, like so:

    57.55%
    14.39%
    28.06%


    Let’s take for example this one for £3447.36 (it’s in C3)

    I’ve made three cells reference that and divide it accordingly:


    =(C3/100)*57.55
    =(C3/100)*14.39
    =(C3/100)*28.06

    It gives me
    £1,983.96
    £496.08
    £967.33

    But if you add those three numbers up it comes to £3447.37

    That’s a penny too much!

    I presume I need to change my formula, but I don’t know how.

    The woman whose job I got split it as:
    £1983.96
    £496.07 there’s your rogue penny
    £967.33

    Which obviously totals £3447.36.

    But she worked on paper (and presumably a calculator) cos she was a biddy.

    Help me out?
    I'm a twit

  27. #77
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,308
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    That's just a rounding error isn't it?

  28. #78
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,019
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jimmy Floyd View Post
    That's just a rounding error isn't it?
    Yes. How do I make it not do that?

    I've fiddled about with the ROUND formula but it keeps coming out as 496.08
    I'm a twit

  29. #79
    Senior Member Jimmy Floyd's Avatar
    Join Date
    Sep 2015
    Posts
    35,308
    Mentioned
    84 Post(s)
    Tagged
    0 Thread(s)
    You need to increase the number of decimal places it's showing, as I think currency format artificially butchers everything to 2DP.

  30. #80
    Senior Member
    Join Date
    Jul 2017
    Posts
    930
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)
    Excel has done the rounding correctly here.

    £3447.36/100*57.55 = 1983.95568
    £3447.36/100*14.39 = 496.075104
    £3447.36/100*28.06 = 967.329216

    These numbers add up to £3447.36 but when rounded, there is a difference of 1p. I don't think Excel can solve this problem.

    Tricky one.

  31. #81
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    It's not really an Excel problem.

    Only thing I can think of is to =ROUND one of the numbers, then use =3477.36-SUM(X1,Y1) to get another rounded figure for the final part.

  32. #82
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,019
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    I doubt it'll always be an issue because every month the invoice amount (3447.36) is different.

    It's gonna be a pain having to manually check the numbers every month rather than just rely on what the spreadsheet spits out at me.
    I'm a twit

  33. #83
    Senior Member
    Join Date
    Feb 2018
    Posts
    422
    Mentioned
    12 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Boom-Boom-18 View Post
    I have 100 points on a chart which require numerical values. If I am able to work out the value for every 15th number, would I be able to use a formula to determine the other values?

    It is based on the values creating a tangential curve.

    0= 0
    1=
    2=
    3=
    4=
    .
    .
    .
    .
    15= 11.75
    16=
    17
    18
    19=
    20=
    21=
    22=
    23=
    24=
    25=
    26=
    27=
    28=
    29=
    30= 27.3
    I have discovered a way of doing this via creating a 'cubic spline' - https://www.youtube.com/watch?v=HpMjdnk-Drg

    Is there a way of doing the same within excel (without having to install an add in) for a linear graph?

    Example, I want to find values for 0=90 and I already have the values for every 15th number, so I plot this onto a graph with straight lines between each of the 15 numbers. I then need a way of taking the values for the numbers in between?

    I believe the add in I downloaded for the cubic spline can do it but I was hoping there was a way of doing this without having to install an add in.

  34. #84
    Senior Member hfswjyr's Avatar
    Join Date
    Sep 2015
    Posts
    942
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    You are trying to interpolate. Just plot the source data and find the linear/cubic equation, then apply it to the remaining data.

    Quote Originally Posted by hfswjyr View Post
    So you are trying to find the formula to relate X and Y?

    Plot the data on a scattergraph in Excel, then:
    https://www.excel-easy.com/examples/trendline.html

  35. #85
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,019
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    I'm sure this is straight forward but I just can't get my head around it. Survey results!

    People completed a survey. All the responses were tick-boxes for strongly agree, agree, neither agree nor disagree, disagreee or strongly disagree.

    There were eleven questions.

    I made a spreadsheet with the eleven questions listed over and over again, and then went through with each completed survey in my hand and put a 1 in the corresponding box, depending on what they ticked. Obviously all the results add up to 11.

    Thing is there two groups of people surveyed. Let's call them Group A and Group B. Group B was bigger.

    I've made a sheet that adds up all of Group A's responses and all of Group B's responses.

    For example question 1 in Group A, the results are as follows:

    Code:
    1	3	2	0	0
    Same question for Group B:

    Code:
    10	3	0	0	0
    I can glance at this and see that six people from group A responded, and thirteen from group B did.

    What I want to do is work out an average, but I don't know what I actually mean by that.

    I guess I want to know, on average, which option was ticked the most. I suspect it will be 'strongly agree,' but I'd like numbers to back it up.

    Am I right in thinking the results will be spread across the five options will all add up to 1? So like:

    Code:
    0.6	0.3	0.05	0.05	0
    How can I accomplish this? I know I can divide it by 13 but I only know this from counting them. How can I automate adding up how many responses I get, cos more come in every day.

    Or can anyone think of a better way to show the results? This one is a warmup really. I may end up with a massive (thousands of responses!) similar task sometime next year, so would like to get my head around this one first.
    Last edited by Baz; 18-12-2018 at 01:48 PM.
    I'm a twit

  36. #86
    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)
    Just use Google Forms?

    There seem to be about a million ways to achieve what you're trying to do, so it's hard to be specific. Sum the numbers? Calculate a percentage? You could probably use COUNTA to work out how many people are in each group and figure something out - but I'm still not sure exactly what you're trying to achieve

  37. #87
    Isn't he banned? Baz's Avatar
    Join Date
    Aug 2015
    Posts
    15,019
    Mentioned
    199 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Raoul Duke View Post
    but I'm still not sure exactly what you're trying to achieve
    Me neither.
    I'm a twit

  38. #88
    Custom User Title phonics's Avatar
    Join Date
    Aug 2015
    Posts
    18,152
    Mentioned
    118 Post(s)
    Tagged
    0 Thread(s)
    I've got an Excel sheet that has a column with country of origin, how can I get it to work out how many times each country appears across the full column?

  39. #89
    Senior Member -james-'s Avatar
    Join Date
    Sep 2015
    Posts
    5,576
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    If your column of data is in A, copy the lot to column B and go data -> remove duplicates on it. Put this in C1 and drag down =COUNTIF(B1, A:A).

  40. #90
    Custom User Title phonics's Avatar
    Join Date
    Aug 2015
    Posts
    18,152
    Mentioned
    118 Post(s)
    Tagged
    0 Thread(s)
    Cheers. I passed off this information to a colleague without acknowledging the help I got. Going to hope they don't now think I know what to do.

  41. #91
    Senior Member Bernanke's Avatar
    Join Date
    Sep 2015
    Posts
    2,471
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Is it possible to have conditional formatting in a cell based on the value of another cell when the latter isn't a single specific one?

    I want to highlight cells in column X based on if a text shows up in column Y, but limited to say "X27 is highlighted if the text is in Y27", so row-specific.

  42. #92
    Senior Member
    Join Date
    Sep 2015
    Posts
    7,745
    Mentioned
    37 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Bernanke View Post
    Is it possible to have conditional formatting in a cell based on the value of another cell when the latter isn't a single specific one?

    I want to highlight cells in column X based on if a text shows up in column Y, but limited to say "X27 is highlighted if the text is in Y27", so row-specific.
    Yup - easy. Just enter a normal IF statement in the conditional formatting box. TRUE will apply the formatting, FALSE will not.

  43. #93
    Senior Member Bernanke's Avatar
    Join Date
    Sep 2015
    Posts
    2,471
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to build a ranking list which accounts for negative values and automatically ranks them lowest on the list, ideally giving them a bottom rank instead of giving them the ranking just below last positive, is there anyway to achieve this?

    The goal for example would be to have the following:

    −100,5 with rank 10
    12,6 with rank 2
    6,2 with rank 1
    41,7 with rank 3
    −20,9 with rank 10
    225,9 with rank 5
    47,9 with rank 4
    −22 with rank 10
    −71,6 with rank 10
    −20 with rank 10

    My first draft was this:

    =RANK.AVG(IF(A1<0,(MAX(A:A),A1);A1:A10;1)

    But then the negative numbers still get a rank 5...

  44. #94
    Senior Member Offshore Toon's Avatar
    Join Date
    Sep 2015
    Posts
    5,566
    Mentioned
    20 Post(s)
    Tagged
    0 Thread(s)
    Does anyone use Alteryx on here? It's quite a steep learning curve but I've been told it'll reap dividends.

  45. #95
    Custom User Title phonics's Avatar
    Join Date
    Aug 2015
    Posts
    18,152
    Mentioned
    118 Post(s)
    Tagged
    0 Thread(s)
    Hate to do this but...

    Graphic Designer who's bad at math here. I have 28 squares that need to fit within a 1920x1080 pixel box. How many pixels should each square be to fit?

  46. #96
    Senior Member niko_cee's Avatar
    Join Date
    Sep 2015
    Posts
    17,951
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)
    Something where you have 4 rows and 7 columns is probably your best bet, but you'd have 30 pixels left over at the end if you did that with 270 x 270 squares. Not sure you can actually completely fill it and still have the things as true squares, if that makes sense.

  47. #97
    Custom User Title phonics's Avatar
    Join Date
    Aug 2015
    Posts
    18,152
    Mentioned
    118 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by niko_cee View Post
    Something where you have 4 rows and 7 columns is probably your best bet, but you'd have 30 pixels left over at the end if you did that with 270 x 270 squares. Not sure you can actually completely fill it and still have the things as true squares, if that makes sense.

    Perfect. Thank you.

  48. #98
    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)
    This one has me stumped.

    I have 4 tables on a sheet. I have another sheet where I want a drop down menu, and based on the selection in that menu I want the relevant table to appear. So if I pick A then table A appears, B then table B, etc. I've managed to make the drop down and can populate the first one with a VLOOKUP, but that's probably not the right way to go about it as the lookup can only be used for the one data set.

    Any ideas? There may be a function to do just that and I don't know about it. Or do I need to reference the drop down list to a value that will change the VLOOKUP formula some way based on the selection?

  49. #99

    Join Date
    Jan 2022
    Posts
    6,297
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)
    You might need to use INDIRECT if you're stuck to using tables.

    =INDIRECT(A1&"[#All]")

    where A1 is your drop-down cell containing the table name.

  50. #100
    Senior Member
    Join Date
    Sep 2015
    Posts
    7,745
    Mentioned
    37 Post(s)
    Tagged
    0 Thread(s)
    Easiest way would to be create a mirror copy of all the tables onto your sheet, then create a macro that hides certain rows depending on the selection in your dropdown menu.

    Something along the lines of

    Worksheets(“Sheet1”).Range(“RowNumber:RowNumber”). EntireRow.Hidden = False


Posting Permissions

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