Excel Quiz Of The Day

[Boom-Boom]

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


I have a piece of plastic that is bought in lengths of 1000mm. I need to cut this plastic into the following lengths (in a variety of quantities).

232.5mm
346.5mm
179mm
533mm
91mm
410mm
851.5mm
335mm
723mm

I have a spread sheet where I've worked out various cutting lists ie.
Cutting List 1 = 232.5, 232.5, 232.5, 232.5mm (930)
Cutting List 2 = 851.5mm, 91mm (942.5)
etc. etc.

I have worked out these lists using predicted usage while trying to keep scrap to a minimum. I've got about 20 lists in total.

I now want to create a formula where I can enter values next to each of the cutting lists and it will tell me the totals of each piece I will get. So for example if I cut the following lists X amount of times
Cutting List 1 = 242 Runs
Cutting List 2 = 12 Runs
Cutting List 3 = 64 Runs
and so on....

When I enter the amount of runs I want it to tell me that I would get XXX amount of 232.5mm etc.
The lists will have cross overs. For example, 232.5mm appears on 7 different lists.



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

[Luca]

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).

[Magic]

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.

[Magic]

Nevermind, just made a text field with the @companyname.com and added that in to concatenate.

[Giggles]

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.

[Magic]

:cool:

[Jimmy Floyd]

Put @whatever.com in cell C1 (say) and then:

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

[Magic]

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?

[Magic]

Format cells, custom, type = 2000.

:cool:

[Magic]

Oh for fuck's sake all the first names have white space at the end! Can I remove this en-masse?!

[Jimmy Floyd]

=TRIM. Only works on later versions though.

[Magic]

I've got 2016 so it worked a treat. Thanks. Just copied and pasted the TRIM'd values.

[Danny]

=trim(Cell that needs trimming

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

[Bernanke]

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?

[Adamski]

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.

[Jimmy Floyd]

If you're typing a formula, then the formula is king.

[Bernanke]

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".

[Jimmy Floyd]

There is, by starting again.

[John Arne]

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.

[Adamski]

Ah that's not bad actually, nice one.

[Bernanke]

Damn, that actually does it! It might not be the cleanest way, but it's nice to learn the option at least.

[Baz]

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.

[-james-]

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.

[hfswjyr]

On my phone at the moment, but you could try rounddown and vlookup.

[John Arne]

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;

http://imgur.com/C3l8syc.jpg


http://imgur.com/hdwxCfB.jpg

[el_rhysio]

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.

[John Arne]

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!).

[Baz]

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

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.

[hfswjyr]

Not sure why you can't just use a simple vlookup? Here you go.

https://drive.google.com/open?id=0B2lbSKluuIzHU2Z2YkQtZFFOYUU

[John Arne]

Excel porn (keyboard shortcuts).

http://www.businessinsider.com/excel-keyboard-shortcuts-windows-and-mac-2017-5?utm_content=buffer15548&utm_medium=social&utm_source=facebook.com&utm_campaign=buffer-bi/#worksheets-and-workbooks-1

[-james-]

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?

[hfswjyr]

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.

[Weaver]

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.

[-james-]

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

[-james-]

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.

[Bernanke]

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.

[Jimmy Floyd]

Concatenate is your friend.

[Raoul Duke]

Data -> text to columns should do it as well. Top, top Excel function that :drool:

[-james-]

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.

[-james-]

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.

[Baz]

Anyone fancy making this spreadsheet (https://docs.google.com/spreadsheets/d/1cRCc6hXmylrQu4CUpKUFMmWAItmn6Z0SmaNWz3Gjq0M/edit?usp=sharing) 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. :D

[Jimmy Floyd]

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.

[hfswjyr]

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

[-james-]

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?

[-james-]

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

:)

[Dan]

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.

[-james-]

Can you explain it differently? I don't think I understand. What are A1, B1, C1, D1?

[Giggles]

Yeah I've no idea what you're asking either.

[Dan]

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 :D

[Foe]

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.

[Foe]

Actually, even multiple if statements would do it.

[Dan]

I don’t know what any of those things are :D

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

[hfswjyr]

https://docs.google.com/spreadsheets/d/1beI9KCEBXJ9BzBHkYbMc2VoH5YM8fMYotWB_7_2gsSI/edit?usp=sharing

Is this what you mean?

Not 100% sure about your explanation of the problem. Maybe try using real terms.

[Dan]

It’s alright, if statements did the job.

[Baz]

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.

[John Arne]

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

[hfswjyr]

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)

[Baz]

Cheers boys.

It’s an old spreadsheet so column F is required so will use the combination.

[Baz]

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?

[Jimmy Floyd]

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.

[Boydy]

http://automatetheboringstuff.com

[hfswjyr]

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),")")

[phonics]

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.

[-james-]

http://automatetheboringstuff.com


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.

[phonics]

I've been googling how to do my job for 10 years now.

[Boydy]

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.

[Giggles]

Is that website the best way for a complete beginner to get into python?

[Boydy]

Yeah, probably.

[Giggles]

Must have a better look through it.

[Boom-Boom-18]

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

[Jimmy Floyd]

You'll need to call Dr Aussie for that one.

[Raoul Duke]

You could probably do it by some kind of (11.75 / COUNTA $A$1:A14).

I'm too tired to figure out the detail.

[hfswjyr]

Are you just trying to interpolate?

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

[Boom-Boom-18]

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

[hfswjyr]

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

[Baz]

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?

[Jimmy Floyd]

That's just a rounding error isn't it?

[Baz]

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

[Jimmy Floyd]

You need to increase the number of decimal places it's showing, as I think currency format artificially butchers everything to 2DP.

[Dave.]

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

[hfswjyr]

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.

[Baz]

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.

[Boom-Boom-18]

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.

[hfswjyr]

You are trying to interpolate. Just plot the source data and find the linear/cubic equation, then apply it to the remaining data.


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

[Baz]

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:



1 3 2 0 0


Same question for Group B:



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

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:



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

[Raoul Duke]

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

[Baz]

but I'm still not sure exactly what you're trying to achieveMe neither. :(

[phonics]

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?

[-james-]

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).

[phonics]

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.

[Bernanke]

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.

[John Arne]

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.

[Bernanke]

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

[Offshore Toon]

Does anyone use Alteryx on here? It's quite a steep learning curve but I've been told it'll reap dividends.

[phonics]

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?

[niko_cee]

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.

[phonics]

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.

[Giggles]

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?

[Ben]

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.

[John Arne]

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

https://gyazo.com/d4b6e25e1a0fc2b34822a71b5676b9ca.jpeg

[Giggles]

Cheers :uhoh:
It would only be a ‘nice to have’ so I think the 4 can just go on the one page

[Boydy]

1556266526482776070

:cab:

[Ben]

The full version. :cool:


https://www.youtube.com/watch?v=x1RVNGDSdw4

[Baz]

Porn, that. :drool:

[Shindig]

Wearing a suit to eSport :cool:

[Bernanke]

I am struggling with a database table where I am trying to compare the quality of three data sources, where one is the "core" one and the other two are being compared against it, since it is set up in an odd way:

id reference source date
1 39.528 1 1 September, 2022, 14:00
2 39.528 2 5 September, 2022, 04:00
3 39.528 3 6 September, 2022, 04:00
4 43.451 1 13 August, 2022, 01:00
5 43.451 2 30 August, 2022, 10:00
6 43.451 3 30 August, 2022, 21:00
7 43.585 1 20 August, 2022, 21:00
8 43.585 2 25 August, 2022, 05:00
9 43.585 3 25 August, 2022, 05:00

I have thousands of rows like this, and I'm trying to compare the average/median time difference of 2 to 1 with the average/median difference of 3 to 1, but I can't figure out how to group them like that in a pivot. :(

Edit: Writing out a table worked well... :moop:

[-james-]

Could you duplicate the reference column, remove duplicates from that, calculate the difference for each source and then average?