Actually, even multiple if statements would do it.
Printable View
Actually, even multiple if statements would do it.
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.
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.
It’s alright, if statements did the job.
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 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)
Cheers boys.
It’s an old spreadsheet so column F is required so will use the combination.
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?
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.
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),")")
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.
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've been googling how to do my job for 10 years now.
Is that website the best way for a complete beginner to get into python?
Yeah, probably.
Must have a better look through it.
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
You'll need to call Dr Aussie for that one.
You could probably do it by some kind of (11.75 / COUNTA $A$1:A14).
I'm too tired to figure out the detail.
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
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
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?
That's just a rounding error isn't it?
You need to increase the number of decimal places it's showing, as I think currency format artificially butchers everything to 2DP.
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:
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.
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 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.
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:
Same question for Group B:Code:1 3 2 0 0
I can glance at this and see that six people from group A responded, and thirteen from group B did.Code:10 3 0 0 0
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:
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.Code:0.6 0.3 0.05 0.05 0
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
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
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?
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).
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.
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.
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...
Does anyone use Alteryx on here? It's quite a steep learning curve but I've been told it'll reap dividends.
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?
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.
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?
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.
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