anyone know if its possible to change a column of negitive numbers (about 800 numbers) into positive numbers? while i'm at it, is it possible to insert a blank line between each number (ie every other row is blank), is there any way of doing this without doing it line by line? thx for any help...
if that column is the only one with negatives then
edit
replace
find what -
replace with (leave blank)
Or
Insert a column to the right of it, then
data
text to columns
delimited
other -
positive numbers appear in the new column
worked a treat passedit, thanks a million..
Quote from: passedit on February 21, 2008, 12:38:01 PM
if that column is the only one with negatives then
edit
replace
find what -
replace with (leave blank)
Or you could have...
Insert a column to the right of it, then
data
text to columns
delimited
other -
positive numbers appear in the new column
Or
add a formula in the adjacent column which is the previous column multiplied by -1. Drag that formula down the new column.
Then copy the new column and paste special (values only) into a third column.
Or
ask some one else to do it for you.
QuoteOr
ask some one else to do it for you.
Best one of the day! :D
Quoteanyone know if its possible to change a column of negitive numbers (about 800 numbers) into positive numbers?
Do you work for Enron?
Quote from: armaghniac on February 21, 2008, 01:56:33 PM
Quoteanyone know if its possible to change a column of negitive numbers (about 800 numbers) into positive numbers?
Do you work for Enron?
ha ha armaghniac, they certainly knew how to make positives out of negitives. much more boring what i'm at ;D ;D
Quote from: magickingdom on February 21, 2008, 02:42:00 PM
Quote from: armaghniac on February 21, 2008, 01:56:33 PM
Quoteanyone know if its possible to change a column of negitive numbers (about 800 numbers) into positive numbers?
Do you work for Enron?
ha ha armaghniac, they certainly knew how to make positives out of negitives. much more boring what i'm at ;D ;D
Very topical, how many years ago was that, 5??
QuoteVery topical, how many years ago was that, 5??
Some of us are old, Benny, I remember when people fiddled Lotus 123. And if I knew who was fiddling the books today, I'd sell their shares or something. ;D
yet another question..
if i have information in 300 rows of a spreadsheet and i wish to insert a clear row every second row any quick way of doing it? eg row 1-300 has info need to insert a row at 2,4,6,8...300. thanks if anyone can help
Yeah new column at A.
Within your existing Data and in the new column inserted enter 1. The cell below it enter 3. Then hightlight both and drag bottom right coner of data to complete odd numbers in new column all the way down to the bottom of your data.
The still in column A enter 2,4 and drag all the way down to about 600.
Then Data sort by column A. you should now have your existing data and new rows in between.
Now delete column A
Quote from: Bensars on September 30, 2008, 02:36:55 PM
Yeah new column at A.
Within your existing Data and in the new column inserted enter 1. The cell below it enter 3. Then hightlight both and drag bottom right coner of data to complete odd numbers in new column all the way down to the bottom of your data.
The still in column A enter 2,4 and drag all the way down to about 600.
Then Data sort by column A. you should now have your existing data and new rows in between.
Now delete column A
bensars, how can i enter 1 in my column with data without losing it? some of my data are numbers
Yeah, Bensars thing works alright.
Insert a column after cellA1, then drag across the bottom of cellB2 until whatever Column 600 is.
Your basically adding a new column at edge. Call it a margin if you will. You will then remove it again.
It wont effect your data, formulas or anything else
Quote from: Bensars on September 30, 2008, 02:36:55 PM
Yeah new column at A.
Within your existing Data and in the new column inserted enter 1. The cell below it enter 3. Then hightlight both and drag bottom right coner of data to complete odd numbers in new column all the way down to the bottom of your data.
The still in column A enter 2,4 and drag all the way down to about 600.
Then Data sort by column A. you should now have your existing data and new rows in between.
Now delete column A
for some reason cant get it to work. what do you mean by within my existing data bensars? and where do i enter 2,4 etc? sorry!!
Quote from: magickingdom on September 30, 2008, 04:08:10 PM
Quote from: Bensars on September 30, 2008, 02:36:55 PM
Yeah new column at A.
Within your existing Data and in the new column inserted enter 1. The cell below it enter 3. Then hightlight both and drag bottom right coner of data to complete odd numbers in new column all the way down to the bottom of your data.
The still in column A enter 2,4 and drag all the way down to about 600.
Then Data sort by column A. you should now have your existing data and new rows in between.
Now delete column A
for some reason cant get it to work. what do you mean by within my existing data bensars? and where do i enter 2,4 etc? sorry!!
magickingdom, bensars method does work.
first, insert a new blank column in column A. (I think you might have missed this step)
next, in this new column and beside first row of your data (probably cell A1), insert 1, and directly below this (cell A2) enter 3.
next, highlight these two cells and drag to the bottom of your data range. This will make Column A a series of 1,3,5,7,9,11 ....
next, in the cell in column A and below your data range (ie. where all the rows are blank), insert 2, and in cell below insert 4.
next, highlight these 2 cells and drag way down to about row 600 or more, so you have a series of 2,4,6,8,10,12......
then highlight the entire sheet, and hit Data-Sort-Column A. Then delete Column A.
It should work.
Quote from: stevo-08 on September 30, 2008, 04:23:38 PM
Quote from: magickingdom on September 30, 2008, 04:08:10 PM
Quote from: Bensars on September 30, 2008, 02:36:55 PM
Yeah new column at A.
Within your existing Data and in the new column inserted enter 1. The cell below it enter 3. Then hightlight both and drag bottom right coner of data to complete odd numbers in new column all the way down to the bottom of your data.
The still in column A enter 2,4 and drag all the way down to about 600.
Then Data sort by column A. you should now have your existing data and new rows in between.
Now delete column A
for some reason cant get it to work. what do you mean by within my existing data bensars? and where do i enter 2,4 etc? sorry!!
magickingdom, bensars method does work.
first, insert a new blank column in column A. (I think you might have missed this step)
next, in this new column and beside first row of your data (probably cell A1), insert 1, and directly below this (cell A2) enter 3.
next, highlight these two cells and drag to the bottom of your data range. This will make Column A a series of 1,3,5,7,9,11 ....
next, in the cell in column A and below your data range (ie. where all the rows are blank), insert 2, and in cell below insert 4.
next, highlight these 2 cells and drag way down to about row 600 or more, so you have a series of 2,4,6,8,10,12......
then highlight the entire sheet, and hit Data-Sort-Column A. Then delete Column A.
It should work.
got it!!! thanks lads...
Excelllent question magickingdom ;)
magickingdom please tell me you aren't working for Brian Lenihan. ;)
no muppet, but i did 'find' a laptop from the dept of finance a while back... ;D
Quote from: Onion Bag on September 30, 2008, 04:40:32 PM
Excelllent question magickingdom ;)
and there was i feeling stupid!
Fair play to you buckos - Im very much a clicker of buttons myself. :-\
anyone know. . .
if you have a formula in a cell but what the cell to remain blank (as opposed to showing zero) if the total of the cell is zero. eg if the total comes to 8 the cell shows 8 but if the total comes to zero it remains blank
thanks for any help
Quote from: magickingdom on October 04, 2009, 04:59:16 PM
anyone know. . .
if you have a formula in a cell but what the cell to remain blank (as opposed to showing zero) if the total of the cell is zero. eg if the total comes to 8 the cell shows 8 but if the total comes to zero it remains blank
thanks for any help
Use conditional formating to turn the font in the cell to white if the value equals zero is one way. I think there is another way to.
tools
options
view
untick zero values
thanks guys both them work a treat. .
Quote from: Orior on October 04, 2009, 10:07:12 PM
Quote from: passedit on October 04, 2009, 06:13:13 PM
tools
options
view
untick zero values
Smart arse.
;D
Quote from: Bing Crosby . on September 29, 2009, 04:43:24 PM
Quote from: passedit on September 29, 2009, 10:25:34 AM
(http://www.google.co.uk/images?q=tbn:KbD5R_aVsFWXNM::www.manchestereventsguide.co.uk/img/full/pop/waiting%252520for%252520godot%252520front.JPG&h=86&w=128&usg=__zec-pnXCq0Stuai0Bf7zvey15ZU=)
Dowling and heffo ponder Frank's 'impending' retirement.
What a dull person you must be passedit .
Quote from: passedit on October 04, 2009, 06:13:13 PM
tools
options
view
untick zero values
Or you could use =IF(A:1=0,"",A:1) if the cell in question was A:1
anyone know were there are excel courses?
would like to get the hang off it a little more, all a know is a few basic formulae etc
Quote from: doirebhoy on October 05, 2009, 12:42:06 PM
anyone know were there are excel courses?
would like to get the hang off it a little more, all a know is a few basic formulae etc
i rarely see any courses advertised but if you mess around with it at home and post any questions here i find that works pretty well!
i have a column of positive numbers (about 400) and i want to make them all negitive. if anyone knows how to do that thanks
1) Insert a blank column beside it
2) If A2 has the first positive number then in B2 enter =(A2*-1)
3) Copy the formula down the entire 400 rows
4) Copy and paste special column b to hard code the data
5) Delete the positive numbers column (A) if you don't want/need it
Dame Julie Andrews will assist - "let's start @ the very beginning, a very good place to start"
Quote from: Caid on October 09, 2009, 02:55:14 PM
1) Insert a blank column beside it
2) If A2 has the first positive number then in B2 enter =(A2*-1)
3) Copy the formula down the entire 400 rows
4) Copy and paste special column b to hard code the data
5) Delete the positive numbers column (A) if you don't want/need it
If column A is already referred to in a formula anywhere, copy column B and paste special (formula as values) back into column A
Quote from: Treasurer on October 09, 2009, 03:10:11 PM
Quote from: Caid on October 09, 2009, 02:55:14 PM
1) Insert a blank column beside it
2) If A2 has the first positive number then in B2 enter =(A2*-1)
3) Copy the formula down the entire 400 rows
4) Copy and paste special column b to hard code the data
5) Delete the positive numbers column (A) if you don't want/need it
If column A is already referred to in a formula anywhere, copy column B and paste special (formula as values) back into column A
That's a fair point. You also just enter the value -1 in a random cell somewhere. Copy the cell and over your 400 data items click paste special and multiply (under the operation part of the past special dialog box). This will multiply all the positives by -1
Quote from: Caid on October 09, 2009, 03:12:58 PM
Quote from: Treasurer on October 09, 2009, 03:10:11 PM
Quote from: Caid on October 09, 2009, 02:55:14 PM
1) Insert a blank column beside it
2) If A2 has the first positive number then in B2 enter =(A2*-1)
3) Copy the formula down the entire 400 rows
4) Copy and paste special column b to hard code the data
5) Delete the positive numbers column (A) if you don't want/need it
If column A is already referred to in a formula anywhere, copy column B and paste special (formula as values) back into column A
That's a fair point. You also just enter the value -1 in a random cell somewhere. Copy the cell and over your 400 data items click paste special and multiply (under the operation part of the past special dialog box). This will multiply all the positives by -1
Oh! Never used that before.
anyone know how to keep a column in view if your using a lot of columns. eg keep columb a in view while your working on column az? i knew how to do it in my old version of excel but not this one. . thanks
Quote from: magickingdom on December 14, 2009, 05:34:24 PM
anyone know how to keep a column in view if your using a lot of columns. eg keep columb a in view while your working on column az? i knew how to do it in my old version of excel but not this one. . thanks
go to view, freeze pane, then freeze first pane
thanks sb, in my version freeze pane is under window but worked a treat
Guys
doing a spreadsheet and i need to input phone numbers, it keeps missing out the ) at the start, not really a big issue but its annoying me... any answers?
What you mean the )? You mean like (028) 71XX XXXX?
Quote from: Square Ball on January 26, 2010, 07:40:28 PM
Guys
doing a spreadsheet and i need to input phone numbers, it keeps missing out the ) at the start, not really a big issue but its annoying me... any answers?
Format the cell as text?
Quote from: Mhic Easmuint on January 26, 2010, 07:45:20 PM
Quote from: Square Ball on January 26, 2010, 07:40:28 PM
Guys
doing a spreadsheet and i need to input phone numbers, it keeps missing out the ) at the start, not really a big issue but its annoying me... any answers?
Format the cell as text?
Yeh, Thats what you do. Right click on cell, select format and select text from the list.
I'd say he means that it's missing out the first zero (shift key held down while pressing 0). This can be solved by putting a ' before the number i.e. '012345
The ' basically tells excel to show what you type and not to mess with it.
Thanks, yeah I was trying to put mobile numbers in and it wouldnt accept the 0 in the 077, so I just formatted as text, ta
My objective for 2010: get the hang of VLOOKUPS.
nowt got to do with excel.... mircosoft word!
need to finish an essay but whatever i have done, when i go back to correct a word it eats up the other word and i lose it....
please help... going mad :'(
zigsters a legend.......................... 8)
Quote from: leenie on January 27, 2010, 01:16:00 AM
nowt got to do with excel.... mircosoft word!
need to finish an essay but whatever i have done, when i go back to correct a word it eats up the other word and i lose it....
please help... going mad :'(
I assume it was the insert key on the keyboard?
yes (duh to me)
i was freaking out....under pressure!
Quote from: leenie on January 27, 2010, 01:28:31 AM
zigsters a legend.......................... 8)
I don't hear that enough ;D
This is a great thread. :)
Quote from: Aaron Boone on January 26, 2010, 10:24:01 PM
My objective for 2010: get the hang of VLOOKUPS.
Yes, very handy - just remember to have your lookup lists sorted alphabetically.
Quote from: Billys Boots on January 27, 2010, 09:28:58 AM
Quote from: Aaron Boone on January 26, 2010, 10:24:01 PM
My objective for 2010: get the hang of VLOOKUPS.
Yes, very handy - just remember to have your lookup lists sorted alphabetically.
Don't see how sorting them is important? Unless you have duplicates in which case it will take the first one.
Quote from: RMDrive on January 27, 2010, 10:24:27 AM
Quote from: Billys Boots on January 27, 2010, 09:28:58 AM
Quote from: Aaron Boone on January 26, 2010, 10:24:01 PM
My objective for 2010: get the hang of VLOOKUPS.
Yes, very handy - just remember to have your lookup lists sorted alphabetically.
Don't see how sorting them is important? Unless you have duplicates in which case it will take the first one.
They must be sorted in ascending order or you may not get the correct answer.
Quote from: Treasurer on January 27, 2010, 10:47:53 AM
Quote from: RMDrive on January 27, 2010, 10:24:27 AM
Quote from: Billys Boots on January 27, 2010, 09:28:58 AM
Quote from: Aaron Boone on January 26, 2010, 10:24:01 PM
My objective for 2010: get the hang of VLOOKUPS.
Yes, very handy - just remember to have your lookup lists sorted alphabetically.
Don't see how sorting them is important? Unless you have duplicates in which case it will take the first one.
They must be sorted in ascending order or you may not get the correct answer.
As usual, beautifully put T!
Quote from: Treasurer on January 27, 2010, 10:47:53 AM
Quote from: RMDrive on January 27, 2010, 10:24:27 AM
Quote from: Billys Boots on January 27, 2010, 09:28:58 AM
Quote from: Aaron Boone on January 26, 2010, 10:24:01 PM
My objective for 2010: get the hang of VLOOKUPS.
Yes, very handy - just remember to have your lookup lists sorted alphabetically.
Don't see how sorting them is important? Unless you have duplicates in which case it will take the first one.
They must be sorted in ascending order or you may not get the correct answer.
What must be sorted in ascending order? I use this a good bit so I want to make sure I'm not doing something wrong.
If I have ...
Donegal - Green
Derry - Red
Antrim - Saffron
Down - Black
... and I vlookup to them, what does it matter how they are sorted?
If you don't have the counties in alphabetical order, you won't get the right colour when you apply the vlookup function. Or if you do, it's a fluke. Try it.
Quote from: Billys Boots on January 27, 2010, 11:47:07 AM
If you don't have the counties in alphabetical order, you won't get the right colour when you apply the vlookup function. Or if you do, it's a fluke. Try it.
Don't need to try it - I know this isn't true (but I tried it anyway just to make sure ;D ). Excel will search down this list until it finds the text/number you have looked up. Then it will go over the number of columns you specify and give you have the result in that cell. It doesn't care how you sort your data. Duplicates will mean it will take the first one it finds but it doesn't matter how you sort them. Try it.
I tried it - it might be the fact that I'm using Excel-2003, but if i set up a vlookup function to select one of those counties from a dropdown list (as you listed, not alphabetically), and I select each one of the counties manually, the function only supplies the right answer twice. Maybe we're using it in different ways.
Quote from: Billys Boots on January 27, 2010, 12:13:57 PM
I tried it - it might be the fact that I'm using Excel-2003, but if i set up a vlookup function to select one of those counties from a dropdown list (as you listed, not alphabetically), and I select each one of the counties manually, the function only supplies the right answer twice. Maybe we're using it in different ways.
Sounds like it. I'm not using drop down lists so maybe that has an impact. I'm using 2003 as well though.
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
So if you put a 1 in range_lookup (the last number between the brackets) you do need to sort your table in ascending order of the first column. If you put a zero, you don't have to sort, but the penalty is that it returns "N/A" if it doesn't get an exact match. Making range_lookup TRUE (i.e. = 1) always returns a value - it's the nearest approximation to the input value if the table is sorted in ascending order.
So we're both one-third right - excellent solution. :P
Quote from: Billys Boots on January 27, 2010, 11:47:07 AM
If you don't have the counties in alphabetical order, you won't get the right colour when you apply the vlookup function. Or if you do, it's a fluke. Try it.
Nonsense.
Just be sure to use "False" as your last argument. If there is an exact match then it will return the corresponding value, regardless of the order of the data.
Oops, guess I should have read the rest of the thread ::)
Quote from: Hardy on January 27, 2010, 12:21:42 PM
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
So if you put a 1 in range_lookup (the last number between the brackets) you do need to sort your table in ascending order of the first column. If you put a zero, you don't have to sort, but the penalty is that it returns "N/A" if it doesn't get an exact match. Making range_lookup TRUE (i.e. = 1) always returns a value - it's the nearest approximation to the input value if the table is sorted in ascending order.
Sín é. I always use a zero (always trying to find an exact match) so never had to deal with the sorting issue. Good to know though.
Reading through some of the stuff here, it's amazing how slack I've gotten. I used to know all this stuff and now I reckon I'd suffer trying to do it again.
Time to re-familiarise myself with Excel and Access again.
Quote from: ziggysego on January 27, 2010, 04:54:45 PM
Reading through some of the stuff here, it's amazing how slack I've gotten. I used to know all this stuff and now I reckon I'd suffer trying to do it again.
Time to re-familiarise myself with Excel and Access again.
I'm an access-nerd, I love it. Although I don't get to use it as much as I used to in my old job :(
Right, in a list I want it to count y, I have tried to do count (y) and a few others but it wont work, I blame user error >:(. I up graded to 2007 a while ago and a few things have changed
Quote from: Square Ball on January 27, 2010, 05:58:44 PM
Right, in a list I want it to count y, I have tried to do count (y) and a few others but it wont work, I blame user error >:(. I up graded to 2007 a while ago and a few things have changed
Use countif, ie countif(range,criteria), put the criteria in inverted commas if it's text you're looking at, eg
=COUNTIF(A1:A6,"dog")
Quote from: Treasurer on January 27, 2010, 10:47:25 PM
Quote from: Square Ball on January 27, 2010, 05:58:44 PM
Right, in a list I want it to count y, I have tried to do count (y) and a few others but it wont work, I blame user error >:(. I up graded to 2007 a while ago and a few things have changed
Use countif, ie countif(range,criteria), put the criteria in inverted commas if it's text you're looking at, eg
=COUNTIF(A1:A6,"dog")
So it's =countif(A1:A25,"y") or =countif(A1:A25,"*y*"). That'll count the number of cells with a y in it. It won't count the total number of ys in the range though (if there are more than two in any cell).
Why would you have 2 y's in a cell? Explain what way the data is set out.
I dunno Mario, I'm just allowing that the 'data' might be text.
Quote from: Billys Boots on January 28, 2010, 09:56:08 AM
I dunno Mario, I'm just allowing that the 'data' might be text.
Sorry Billy Boots, got confused there, thought you where the person with the problem and not the person with the solution.
Quote from: Treasurer on January 27, 2010, 05:40:33 PM
Quote from: ziggysego on January 27, 2010, 04:54:45 PM
Reading through some of the stuff here, it's amazing how slack I've gotten. I used to know all this stuff and now I reckon I'd suffer trying to do it again.
Time to re-familiarise myself with Excel and Access again.
I'm an access-nerd, I love it. Although I don't get to use it as much as I used to in my old job :(
Indeed I remember you helping me with an Access problem I was baffled with and being blown away by the extend of your knowledge on the subject.
Quote from: Billys Boots on January 28, 2010, 09:10:45 AM
Quote from: Treasurer on January 27, 2010, 10:47:25 PM
Quote from: Square Ball on January 27, 2010, 05:58:44 PM
Right, in a list I want it to count y, I have tried to do count (y) and a few others but it wont work, I blame user error >:(. I up graded to 2007 a while ago and a few things have changed
Use countif, ie countif(range,criteria), put the criteria in inverted commas if it's text you're looking at, eg
=COUNTIF(A1:A6,"dog")
So it's =countif(A1:A25,"y") or =countif(A1:A25,"*y*"). That'll count the number of cells with a y in it. It won't count the total number of ys in the range though (if there are more than two in any cell).
OOh good catch, I wasn't thinking of "y" being
part of a piece text.
(I'm easily entertained really)
Yeah, but how would you count the number of 'y's in the text in one or a number of cells, if that was your inclination?
Quote from: Billys Boots on January 29, 2010, 11:25:00 AM
Yeah, but how would you count the number of 'y's in the text in one or a number of cells, if that was your inclination?
I give up! Grrrrrrrr
I dunno either T, I was just asking. I guess it can be done if it needed to be.
Quote from: Billys Boots on January 29, 2010, 02:36:02 PM
I dunno either T, I was just asking. I guess it can be done if it needed to be.
I could do it a very long-winded way with vba but not as a straightforward excel function.
I found a long solution online that used functions I've never touched, but I couldn't even get that to work.
I'm teaching myself vba at the moment - any tips??
Quote from: Billys Boots on January 29, 2010, 02:57:53 PM
I'm teaching myself vba at the moment - any tips??
None! I only know bits and pieces I picked up over the years with access. I went on a forum once looking for assistance and was taken under the wing of an access/vba pro who gave me lots of bits of code etc to use and modify, but I'd be very weak at it really. Always meant to get deeper into it, but don't really have the same need for it any more so the push isn't there to do it.
Quote from: Treasurer on January 29, 2010, 02:41:41 PM
Quote from: Billys Boots on January 29, 2010, 02:36:02 PM
I dunno either T, I was just asking. I guess it can be done if it needed to be.
I could do it a very long-winded way with vba but not as a straightforward excel function.
I found a long solution online that used functions I've never touched, but I couldn't even get that to work.
Not very efficient, write a function to return the number of y's for a given cell & then use the newly defined function in a subroutine (or a function that returns a total count) that iterates though all cells in a given range updating a count variable after every iteration.
Probably easier ways within excel itself and cleverer search algorithms to use but it's probably around 5 lines of code.
Try this, subsituting the range you want to search,
=SUMPRODUCT((LEN(A14:F18)-(LEN(SUBSTITUTE(A14:F18,"y",""))))/LEN("y"))
Think that should be right or close enough.
I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.
Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...
Quote from: donelli on February 02, 2010, 08:52:56 PM
I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.
Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...
Don't have Excel on this computer but off the top of my head a COUNTIF function could probably do this. It could get quite "wordy" though.
hello...hope someone can help me here
I'm creating a pivot table on the data below
Date County Number
01/01 Armagh 1
01/01 Armagh 1
01/01 Armagh 1
01/01 Down 2
01/01 Down 2
02/01 Armagh 3
02/01 Armagh 3
I want the pivot table to look like this below with the number in the middle, however line 1,2 and 3 above are duplciates and i only want them included once, same with line 4 and 5...can this be done?
County
Date
Where the hell has the solver function gone in Excel 2007? just upgraded to that the other day, parts of it are good, but the getting used to it process is highly frustrating.
Quote from: donelli on February 02, 2010, 08:52:56 PM
I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.
Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...
I have exactly that. There's probably a shorter way of doing it than how I did it, but it works. Will dig it out for you tomorrow.
Quote from: bud on February 02, 2010, 09:24:38 PM
hello...hope someone can help me here
I'm creating a pivot table on the data below
Date County Number
01/01 Armagh 1
01/01 Armagh 1
01/01 Armagh 1
01/01 Down 2
01/01 Down 2
02/01 Armagh 3
02/01 Armagh 3
I want the pivot table to look like this below with the number in the middle, however line 1,2 and 3 above are duplciates and i only want them included once, same with line 4 and 5...can this be done?
County
Date
If you go into the pivot table wizard you will see a section where you can drag the data types (date, county, number) into either the column, row or data sections. You need date in the column, county in the row and drag/drop the numnber into the data field. Double ckick on the data field and select average (although min or max will work in this instance). When you click ok you may need to drag the county column up and drop on the row toolbar.
Sorry this might not be clear. HArd to explain
Quote from: donelli on February 02, 2010, 08:52:56 PM
I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.
Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...
Use hlookups, ifiserror and count function. Sounds complicated but actually pretty easy
If you pm me your email i'll send u an example
Quote from: Caid on February 02, 2010, 11:23:03 PM
Quote from: donelli on February 02, 2010, 08:52:56 PM
I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.
Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...
Use hlookups, ifiserror and count function. Sounds complicated but actually pretty easy
If you pm me your email i'll send u an example
Sounds shorer than the way I've done it!
I have about 300 names in an excel spreadsheet. The full name (first name and surname) is in 1 cell.
I'm trying to sort out the surnames in alphabetical order. Is there anyway i can extract the surname from the cells to sort this or will i just have to re-write all the names in 2 cells ??
Cell A1 contains full name
Cell B1 formula should be =LEFT(A1, FIND(" ",A1))
Cell C1 formula should be =RIGHT(A1, LEN(A1) - FIND(" ",A1))
That will give you the forename in B1 and the surname in C1 (assuming that the forename and surname do not contain spaces)
You also col A and do text to columns and tick space.
It will then seperate them after the first spce. Any Names with Mc or O' for example could then be rejoined in an additional columm
Quote from: Orior on February 24, 2010, 09:20:56 PM
Cell A1 contains full name
Cell B1 formula should be =LEFT(A1, FIND(" ",A1))
Cell C1 formula should be =RIGHT(A1, LEN(A1) - FIND(" ",A1))
That will give you the forename in B1 and the surname in C1 (assuming that the forename and surname do not contain spaces)
good stuff. works a treat and will save me an hours work!! cheers
Data, text to columns, delimited space. Might have an issue with the os and mcs tho
Quote from: passedit on February 24, 2010, 09:32:19 PM
Data, text to columns, delimited space. Might have an issue with the os and mcs tho
Sweet. I had actually been thinking of exporting then reimporting but that is much handier.
I learnt something new today.
any mc's and o's can be joined in additional column.
=("b1"&" "&"c1") e.g. colum b =O column c =Neill
Then copy corrected entries and use paste special to paste values only
much like Bensars said, if you end up with O and Mc in B and C then just put CONCATENATE(B1," ",C1) in D1 and copy down.
The following names can also cause problems
Jimmy Barry Murphy
. Cotton (aka Dot Cotton)
Kaka
squiggle (aka Prince from a few years ago)
Quote from: Orior on February 24, 2010, 09:37:04 PM
Quote from: passedit on February 24, 2010, 09:32:19 PM
Data, text to columns, delimited space. Might have an issue with the os and mcs tho
Sweet. I had actually been thinking of exporting then reimporting but that is much handier.
I learnt something new today.
Trust you to find the hard way of doing it Orior. ;)
I hope this is easy...
I want to be able to merge data from 2 columns into one column.
i.e.
Column A = Document Reference e.g. BD001
Column B = Document Revision e.g. Rev.1
I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1
Help would be really appreciated as I have about 1000 rows!
Quote from: Tony Baloney on March 11, 2010, 09:42:13 AM
I hope this is easy...
I want to be able to merge data from 2 columns into one column.
i.e.
Column A = Document Reference e.g. BD001
Column B = Document Revision e.g. Rev.1
I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1
Help would be really appreciated as I have about 1000 rows!
Try,
=concatenate(A1, " ", B1)
Quote from: Tony Baloney on March 11, 2010, 09:42:13 AM
I hope this is easy...
I want to be able to merge data from 2 columns into one column.
i.e.
Column A = Document Reference e.g. BD001
Column B = Document Revision e.g. Rev.1
I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1
Help would be really appreciated as I have about 1000 rows!
Or =A1 & " " & B1
Ampersand is a great word.
I googled it. Cheers anyway. Sorted.
In cell C1, put =A1&TEXT(B1,0). Copy down the way.
How many ways can come up with to solve the same problem?
Quote from: saffron sam2 on March 11, 2010, 09:57:37 AM
Quote from: Tony Baloney on March 11, 2010, 09:42:13 AM
I hope this is easy...
I want to be able to merge data from 2 columns into one column.
i.e.
Column A = Document Reference e.g. BD001
Column B = Document Revision e.g. Rev.1
I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1
Help would be really appreciated as I have about 1000 rows!
Or =A1 & " " & B1
Ampersand is a great word.
I'm thinking of calling my next child "Ampersand", middle name Dot. And when she married Joe Barr, her email address will be
&.@|
Quote from: Orior on March 11, 2010, 11:12:05 AM
Quote from: saffron sam2 on March 11, 2010, 09:57:37 AM
Quote from: Tony Baloney on March 11, 2010, 09:42:13 AM
I hope this is easy...
I want to be able to merge data from 2 columns into one column.
i.e.
Column A = Document Reference e.g. BD001
Column B = Document Revision e.g. Rev.1
I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1
Help would be really appreciated as I have about 1000 rows!
Or =A1 & " " & B1
Ampersand is a great word.
I'm thinking of calling my next child "Ampersand", middle name Dot. And when she married Joe Barr, her email address will be
&.@|
You get stranger and stranger!
Quote from: Orior on March 11, 2010, 11:12:05 AM
Quote from: saffron sam2 on March 11, 2010, 09:57:37 AM
Quote from: Tony Baloney on March 11, 2010, 09:42:13 AM
I hope this is easy...
I want to be able to merge data from 2 columns into one column.
i.e.
Column A = Document Reference e.g. BD001
Column B = Document Revision e.g. Rev.1
I want to merge both into a single column i.e.
Column C = Document Reference + Document Version e.g. BD001 Rev.1
Help would be really appreciated as I have about 1000 rows!
Or =A1 & " " & B1
Ampersand is a great word.
I'm thinking of calling my next child "Ampersand", middle name Dot. And when she married Joe Barr, her email address will be
&.@|
You could call him Dubya.
Then it would be W Orior.
Cool.
I have 10 sheets in my workbook (Coversheet, sheetA, SheetB, SheetC etc)
I want the contents of a cell CoverSheet to be the value of cell A1 in another sheet. The name of the other sheet is held in cell B2.
I was trying the following formula:
CONCATENATE("='",B1,"'!A1") but it does not work. Any ideas?
=CONCATENATE("=",B1,"!","A1")
Quote from: mylestheslasher on April 01, 2010, 08:39:41 PM
=CONCATENATE("=",B1,"!","A1")
It does not recognise the contents of B1 as another worksheet.
I tried putting two single apostophes around B1, but that doesnt work either.
Quote from: Orior on April 01, 2010, 08:57:00 PM
Quote from: mylestheslasher on April 01, 2010, 08:39:41 PM
=CONCATENATE("=",B1,"!","A1")
It does not recognise the contents of B1 as another worksheet.
I tried putting two single apostophes around B1, but that doesnt work either.
I think you need to use full reference to the worksheet and cell e.g. =CONCATENATE("=",sheeta!B1,"!","A1")
Quote from: Orior on April 01, 2010, 08:28:53 PM
I have 10 sheets in my workbook (Coversheet, sheetA, SheetB, SheetC etc)
I want the contents of a cell CoverSheet to be the value of cell A1 in another sheet. The name of the other sheet is held in cell B2.
I was trying the following formula:
CONCATENATE("='",B1,"'!A1") but it does not work. Any ideas?
Give me a better example, concatenate only joins 2 or more strings together. I don't think it is what you want to do.
i will put this on the torrent tread as well but anyone looking windows office here is is one that is easy to install and works a treat
http://thepiratebay.org/torrent/5426553/Microsoft_Office_2007-_Full_Package_-_Easy_Install_-_Unlimited_l (http://thepiratebay.org/torrent/5426553/Microsoft_Office_2007-_Full_Package_-_Easy_Install_-_Unlimited_l)
I think you can use the INDIRECT function in excel
So if you wanted the contents of Sheeta!A1 you could use
=INDIRECT("Sheeta!A1")
Or in your case you would want
=INDIRECT(B2&"!A1")
I'm using excell to put all the lads numbers and email address into but when i put 0 (zero) in for their mob number in deletes the 0 why is this
Quote from: illdecide on April 02, 2010, 11:58:42 AM
I'm using excell to put all the lads numbers and email address into but when i put 0 (zero) in for their mob number in deletes the 0 why is this
Format the cells as text
Quote from: Mhic Easmuint on April 02, 2010, 12:08:40 PM
Quote from: illdecide on April 02, 2010, 11:58:42 AM
I'm using excell to put all the lads numbers and email address into but when i put 0 (zero) in for their mob number in deletes the 0 why is this
Format the cells as text
Cheers...didn't wanna ask anyone in the office incase they asked me what was i doing cause it's certainly not what i'm paid to do...lol
Quote from: Maiden1 on April 02, 2010, 10:45:03 AM
I think you can use the INDIRECT function in excel
So if you wanted the contents of Sheeta!A1 you could use
=INDIRECT("Sheeta!A1")
Or in your case you would want
=INDIRECT(B2&"!A1")
Beautiful.
Quote from: illdecide on April 02, 2010, 12:10:56 PM
Quote from: Mhic Easmuint on April 02, 2010, 12:08:40 PM
Quote from: illdecide on April 02, 2010, 11:58:42 AM
I'm using excell to put all the lads numbers and email address into but when i put 0 (zero) in for their mob number in deletes the 0 why is this
Format the cells as text
Cheers...didn't wanna ask anyone in the office incase they asked me what was i doing cause it's certainly not what i'm paid to do...lol
Or just put an apostrophe before the 0 when you are typing
'0871234567
Two Questions for you :
Has anyone used a Gantt Chart before ? Any advice ?
Also how do you get a minus figure say -10 to look like (10) - I have tried formatting the cells but with no luck
Quote from: Shortso79 on April 14, 2010, 08:03:24 PM
Also how do you get a minus figure say -10 to look like (10) - I have tried formatting the cells but with no luck
Format Cells, then choose Custom in the number menu and type this into the "Type:" box:
#,##0;(#,##0)
cheers joe !
Hi, need help.
From a machine i use in work, i can retrieve data in an excel file.
Its a pulse oximeter/CO2 monitor and when these probes fall off the patient, it doesn't record. However on the excel spreadsheet it records it as -?-
There would maybe be a few hundred of these in a spreadsheet containing thousands of values and when i go to plot the results on a graph these tend to be plotted as 0.
However when i remove them and leave the cell blank i get the required graph.
Is there any way i can, using an excel function, replace all the -?- with a blank cell in one go?
It can be quite tedious doing it manually.
Use the 'replace all' function in the Edit menu. Select the column/row, then hit edit/replace and put "-?-" in the text box and leave the replacement text box empty.
Brilliant. Thanks,
One more thing. These studies take place from when someone goes asleep until they awake in the morning again. When i plot these on the graph it sends the data before midnight to the end of the graph and the data after midnight to the start of the graph.
Is there any way of merging the date and time so that my data will be plotted on the 'x' in the correct order?
Change the format of the date field to a number?
Highlight the column, Right Mouse, Format Cells, on the Number tab, select Number
If you change the format of the 'time' cells to 'time and date' then they should line up sequentially on the x-0axis too, I think.
If you select all the data then click on Data>Sort then choose by date asc then sort by time asc the graph should resort correctly I think.
Quote from: Billys Boots on April 23, 2010, 01:43:33 PM
Use the 'replace all' function in the Edit menu. Select the column/row, then hit edit/replace and put "-?-" in the text box and leave the replacement text box empty.
You might want to put a tilde (~) before the question mark to stop it being treated as a wildcard character.
In a 100% Stacked Column Chart, if I have four columns and each column represents a country, how do I get the country's name to appear underneath the column on the x-axis?
Don't worry, got it sorted.
Chaps,
How do I format cells to report data to a given number of significant figures?
The user will input data into unprotected cells and the results of the formula will be reported in protected cells i.e. 6 significant figures in the results cells. At the minute trailing zeroes are being dropped from my results.
Quote from: magickingdom on February 21, 2008, 12:29:11 PM
anyone know if its possible to change a column of negitive numbers (about 800 numbers) into positive numbers? while i'm at it, is it possible to insert a blank line between each number (ie every other row is blank), is there any way of doing this without doing it line by line? thx for any help...
do you work for AIB?
Quote from: Tony Baloney on May 05, 2010, 02:51:40 PM
Chaps,
How do I format cells to report data to a given number of significant figures?
The user will input data into unprotected cells and the results of the formula will be reported in protected cells i.e. 6 significant figures in the results cells. At the minute trailing zeroes are being dropped from my results.
Format - Cells - Number - Number - Decimal Places
Quote from: Hardy on May 05, 2010, 03:13:18 PM
Quote from: Tony Baloney on May 05, 2010, 02:51:40 PM
Chaps,
How do I format cells to report data to a given number of significant figures?
The user will input data into unprotected cells and the results of the formula will be reported in protected cells i.e. 6 significant figures in the results cells. At the minute trailing zeroes are being dropped from my results.
Format - Cells - Number - Number - Decimal Places
Not as simple as that. The piece I neglected to mention is that the number of decimal places will vary depending on the input data i.e. if I put 0.455410 in cell A1 I need that to dynamically carry through into various other cells. However on occasion someone may need to enter 0.455 into that cell.
What I'm really looking at is something "where number of signficant figures in A1 = 2, 3, 4, 5, or 6 then number of signficant figures in B1, C1... etc. must equal 2, 3, 4, 5 or 6"
Quote from: Tony Baloney on May 05, 2010, 03:56:05 PM
Quote from: Hardy on May 05, 2010, 03:13:18 PM
Quote from: Tony Baloney on May 05, 2010, 02:51:40 PM
Chaps,
How do I format cells to report data to a given number of significant figures?
The user will input data into unprotected cells and the results of the formula will be reported in protected cells i.e. 6 significant figures in the results cells. At the minute trailing zeroes are being dropped from my results.
Format - Cells - Number - Number - Decimal Places
Not as simple as that. The piece I neglected to mention is that the number of decimal places will vary depending on the input data i.e. if I put 0.455410 in cell A1 I need that to dynamically carry through into various other cells. However on occasion someone may need to enter 0.455 into that cell.
What I'm really looking at is something "where number of signficant figures in A1 = 2, 3, 4, 5, or 6 then number of signficant figures in B1, C1... etc. must equal 2, 3, 4, 5 or 6"
why not just format all cells (A1, B1, C1 ......) to 6 decimal places? then it's enforced throughout your worksheet/book?
Quote from: thebigfella on May 05, 2010, 04:37:52 PM
Quote from: Tony Baloney on May 05, 2010, 03:56:05 PM
Quote from: Hardy on May 05, 2010, 03:13:18 PM
Quote from: Tony Baloney on May 05, 2010, 02:51:40 PM
Chaps,
How do I format cells to report data to a given number of significant figures?
The user will input data into unprotected cells and the results of the formula will be reported in protected cells i.e. 6 significant figures in the results cells. At the minute trailing zeroes are being dropped from my results.
Format - Cells - Number - Number - Decimal Places
Not as simple as that. The piece I neglected to mention is that the number of decimal places will vary depending on the input data i.e. if I put 0.455410 in cell A1 I need that to dynamically carry through into various other cells. However on occasion someone may need to enter 0.455 into that cell.
What I'm really looking at is something "where number of signficant figures in A1 = 2, 3, 4, 5, or 6 then number of signficant figures in B1, C1... etc. must equal 2, 3, 4, 5 or 6"
why not just format all cells (A1, B1, C1 ......) to 6 decimal places? then it's enforced throughout your worksheet/book?
I'm not explaining myself very well. I want it to be dynamic not static number of sig figs. If someone inputs data to 3 or 4 or 5 or 6 sig figs I want the other linked, protected cells displaying the results to report the results to the same number of sig figs. Would a length formula work?
If I'm understanding you correctly, the format Number-General should do that - preserve whatever number of decimal places you enter.
Quote from: seafoid on May 05, 2010, 03:08:43 PM
Quote from: magickingdom on February 21, 2008, 12:29:11 PM
anyone know if its possible to change a column of negitive numbers (about 800 numbers) into positive numbers? while i'm at it, is it possible to insert a blank line between each number (ie every other row is blank), is there any way of doing this without doing it line by line? thx for any help...
do you work for AIB?
you could say that with the size of a mortgage i have with them ;)
Quote from: Hardy on May 05, 2010, 07:53:25 PM
If I'm understanding you correctly, the format Number-General should do that - preserve whatever number of decimal places you enter.
except that he wants to carry trailing zeroes?????????
if you are doing a spreadsheet and you have a column with current values and you need to create a new column with for example 10% added BUT when the new 10% extra column is created I want to delete the current price. However when I do this the new field created goes back to zero ???
Copy and paste the new column as values, rather than formulas, before deleting the original price column.
Copy
Paste Special
Values
Quote from: Tony Baloney on May 05, 2010, 05:42:21 PM
Quote from: thebigfella on May 05, 2010, 04:37:52 PM
Quote from: Tony Baloney on May 05, 2010, 03:56:05 PM
Quote from: Hardy on May 05, 2010, 03:13:18 PM
Quote from: Tony Baloney on May 05, 2010, 02:51:40 PM
Chaps,
How do I format cells to report data to a given number of significant figures?
The user will input data into unprotected cells and the results of the formula will be reported in protected cells i.e. 6 significant figures in the results cells. At the minute trailing zeroes are being dropped from my results.
Format - Cells - Number - Number - Decimal Places
Not as simple as that. The piece I neglected to mention is that the number of decimal places will vary depending on the input data i.e. if I put 0.455410 in cell A1 I need that to dynamically carry through into various other cells. However on occasion someone may need to enter 0.455 into that cell.
What I'm really looking at is something "where number of signficant figures in A1 = 2, 3, 4, 5, or 6 then number of signficant figures in B1, C1... etc. must equal 2, 3, 4, 5 or 6"
why not just format all cells (A1, B1, C1 ......) to 6 decimal places? then it's enforced throughout your worksheet/book?
I'm not explaining myself very well. I want it to be dynamic not static number of sig figs. If someone inputs data to 3 or 4 or 5 or 6 sig figs I want the other linked, protected cells displaying the results to report the results to the same number of sig figs. Would a length formula work?
Tony, did you get sorted out with that?
If the Number-General format is not what you're after, the only way I can think of to do it is convert the number to text and parse the text by the position of the decimal point. Of course, then you're left with text. If you need to convert it back to numbers for calculations, then the formatting will be lost again.
Much obliged Hardy.
im having trouble with decimal points,they keep coming up as euro signs,how can i change that?
Quote from: Dougal on May 24, 2010, 09:12:22 PM
im having trouble with decimal points,they keep coming up as euro signs,how can i change that?
Right click, format cells, change it to number as opposed to currency.
I have a table which contains the date of car sales and includes the owners name and manufacturer.
Col A | Col B | Col C |
Date Car Sold | Owners Name | Manufacturer |
20-Jan-10 | Jim | Ford |
19-Jan-10 | Jack | Buick |
22-Jan-10 | Timmy | Toyota |
20-Jan-10 | Anne | Buick |
How do I produce a bar graph which shows a count of the number of cars sold on each date?
Add another column with the amount sold on each date. Then highlight the dates and while holding the Ctrl key highlight the amount sold column. You should then have two cell ranges highlighted. Then look for your chart wizard icon or select Insert|chart and follow the guidelines. Hope this helps
Quote from: townof12 on June 22, 2010, 11:19:56 PM
Add another column with the amount sold on each date. Then highlight the dates and while holding the Ctrl key highlight the amount sold column. You should then have two cell ranges highlighted. Then look for your chart wizard icon or select Insert|chart and follow the guidelines. Hope this helps
Yeah but, no but, yeah but, how do I produce a count of those sold on each day?
Advanced filter (unique records), copy to new range will identify the unique combinations.
=COUNTIF will count the number
I needed to concatenate the columns to get the COUNTIF to work
=CONCATENATE(TEXT(A2,"dd-mm-yy"),B2)
Hi guys,
I have a list of coordinates, Eastings and Northings which, with my limited Excel knowledge I have managed to graph.
Is there any way of finding the area bounded by the points??
Quote from: Never beat the deeler on September 22, 2010, 09:16:22 AM
Hi guys,
I have a list of coordinates, Eastings and Northings which, with my limited Excel knowledge I have managed to graph.
Is there any way of finding the area bounded by the points??
guess not, then :-\
I had a look, but I think you'd need a mapping programme of some sort, for accuracy.
I guess you could do something through the triangulation method, but it wouldn't be automated. How many 'points' are you talking about?
Quote from: Billys Boots on September 22, 2010, 01:16:39 PM
I had a look, but I think you'd need a mapping programme of some sort, for accuracy.
I guess you could do something through the triangulation method, but it wouldn't be automated. How many 'points' are you talking about?
Talking about ten pts, doesnt need to be accurate.... Cheers for reply, dont worry bout it. I can get someone else in the office to do it in CAD
Quote from: Never beat the deeler on September 22, 2010, 01:33:57 PM
Quote from: Billys Boots on September 22, 2010, 01:16:39 PM
I had a look, but I think you'd need a mapping programme of some sort, for accuracy.
I guess you could do something through the triangulation method, but it wouldn't be automated. How many 'points' are you talking about?
Talking about ten pts, doesnt need to be accurate.... Cheers for reply, dont worry bout it. I can get someone else in the office to do it in CAD
Are ye flogging sites out in Rakestreet??
Lads, help needed ASAP. I have a normal graph (XY Scatter with smooth lines) I have a line in the graph following my Y values (vertical axis) at my X points (horizontal axis) and all that is grand and no problem.
but I don't know how to do the following.... at my first X point I have my corresponding Y value to form the first point of the line. But I also want to introduce two other separate Y values to form two other separate points. These two new points along with extising point on the line are all under the same X valve and I want to form a straight line through these three points..
Can anyone help me out???
Quote from: Billys Boots on September 22, 2010, 02:00:09 PM
Quote from: Never beat the deeler on September 22, 2010, 01:33:57 PM
Quote from: Billys Boots on September 22, 2010, 01:16:39 PM
I had a look, but I think you'd need a mapping programme of some sort, for accuracy.
I guess you could do something through the triangulation method, but it wouldn't be automated. How many 'points' are you talking about?
Talking about ten pts, doesnt need to be accurate.... Cheers for reply, dont worry bout it. I can get someone else in the office to do it in CAD
Are ye flogging sites out in Rakestreet??
Woah... that needs to go in the 'Things that make you go WTF' thread!!! What do you know about Rakestreet??
Quote from: Celt_Man on September 22, 2010, 02:01:19 PM
Lads, help needed ASAP. I have a normal graph (XY Scatter with smooth lines) I have a line in the graph following my Y values (vertical axis) at my X points (horizontal axis) and all that is grand and no problem.
but I don't know how to do the following.... at my first X point I have my corresponding Y value to form the first point of the line. But I also want to introduce two other separate Y values to form two other separate points. These two new points along with extising point on the line are all under the same X valve and I want to form a straight line through these three points..
Can anyone help me out???
You mean like a separate line?? Just add another Series in the chart properties, using pt 1 and the other two pts
Quote from: Never beat the deeler on September 22, 2010, 02:26:33 PM
Quote from: Billys Boots on September 22, 2010, 02:00:09 PM
Quote from: Never beat the deeler on September 22, 2010, 01:33:57 PM
Quote from: Billys Boots on September 22, 2010, 01:16:39 PM
I had a look, but I think you'd need a mapping programme of some sort, for accuracy.
I guess you could do something through the triangulation method, but it wouldn't be automated. How many 'points' are you talking about?
Talking about ten pts, doesnt need to be accurate.... Cheers for reply, dont worry bout it. I can get someone else in the office to do it in CAD
Are ye flogging sites out in Rakestreet??
Woah... that needs to go in the 'Things that make you go WTF' thread!!! What do you know about Rakestreet??
Ah now, that'd be telling. :P
Latest problem:
I have a list with recurring values. How fo I count the number of separate values?
eg if the range was 1,1, 3, 4, 5, 6, 6, 7, 7, 10, 10
the number of values would be 7.
I dont want to count non recurring values as that would return 3
Tks
Folks, I've a reasonably simple excel file with four worksheets - it doesn't have any jpegs/bitmaps in it and it's nearly 9MB. What could be adding to its size? There isn't masses of data in it either, and there are no links to anything outside the file.
If you take a copy of the file then delete 1 tab save it and see the size then delete a second tab until you see which tab is causing most of the memory. Then try to narrow it down further, if need be create a new worksheet and copy the information from the worksheet which is causing the huge memory then delete the original. Something may be happening behind the scenes you are not aware off. It may be easiest just to copy all the data to a new spreadsheet and save as sometimes as over time memory can build up if you make changes to the sheet a lot.
Jaysus Billy - Sure there are no hidden columns or rows in there?
I have a feeling it's been 'converted' between Excel 2007 and Excel 2003 a few times - I might copy everything to a blank sheet and start again.
Also if you highlight and format a column the whole way to the bottom (all 65000) e.g. make all the cells date format dd-mmm-yyyy even if you only put a date in the first 5 rows each row the whole way to the bottom will take up memory.
If you go to the data below the cells you are using then highlight the rows right to the bottom then right click and delete this will delete all the formatting in the blank cells and you will see your memory coming away down.
QuoteIf you go to the data below the cells you are using then highlight the rows right to the bottom then right click and delete this will delete all the formatting in the blank cells and you will see your memory coming away down.
Didn't know that one Maiden - Nice one!
Nah, no dates in this data - but thanks, I didn't know that.
For those small few who might be interested, I started a new sheet and cut and pasted everything (within the print area)in the 'big' sheet (without formatting) and re-formatted everything. The file size reduced from 8.4MB to 46KB. I tried to work out by gradually doing everything and saving etc. and what seemed to have been the problem was that the background colour in one of the blank sheets had been changed from white to grey (on the whole page). Mad.
I've got a fairly complex spreadsheet and trying to count certain things....
If column B contains certain txt, I want to count the no of values in Col X minus the no of cells containing certain words.
I've tried.....
=IF($B:$B="Sample text",(COUNTA(QM$14:QM$213)-COUNTIF(QM$14:QM$213,"FOA")-COUNTIF(QM$14:QM$213,"FOP")-COUNTIF(QM$14:QM$213,"AL")-COUNTIF(QM$14:QM$213,"CL")-COUNTIF(QM$14:QM$213,"S")-COUNTIF(QM$14:QM$213,"T")),"")
Im making a guest list and in each cell im putting the name of the person to be invited. I will have different columns with different peoples names. Is there any way I can keep the name of the person but add a value? Reason is so when ive all the guests entered on the spreadsheet, Excel will calculate how many people ive asked so each person will only need a value of 1?
Im an Excel buffon and have no idea how to do this ???
Quote from: Mac Eoghain on January 04, 2011, 11:54:57 AM
The formula you need is =counta(RANGE) - this will give you the number of non-empty cells.
Thanks!!
=IF(O24<100,"10",IF(O24<200,"20",IF(O24<300,"30",IF(O24<400,"40", "50"))))
Quote from: Orior on January 05, 2011, 12:06:32 PM
=IF(O24<100,"10",IF(O24<200,"20",IF(O24<300,"30",IF(O24<400,"40", "50"))))
Good man orior. Saved me a headache
I want to give some a spreadshhet to input values into A1 and B1. C1 = A1 x B1
i want to protect C1 so no one can alter it. I have tried to use cell protect but it then restricts input to A1 and B1 which defeats the purpose of my exercise. Any suggestions?
Quote from: gerrykeegan on February 09, 2011, 11:20:19 AM
I want to give some a spreadshhet to input values into A1 and B1. C1 = A1 x B1
i want to protect C1 so no one can alter it. I have tried to use cell protect but it then restricts input to A1 and B1 which defeats the purpose of my exercise. Any suggestions?
1. Protect the workbook in tools > protection
2. Highlight the cells you want people to be able to change in your case A1 and B1, click format > cells > go to protection tab > make sure the locked cell box
isnt ticked, do the same for cell c1, this time make sure it is ticked
3. now protect the sheet in tools >protection
gentleman, thanks
Hi There,
Just wondering if someone can help me with this excel question.
I need to write a formula to give me a score based on an achievement.
The achievement range is 80 - 100 -110 and the score range is 1 - 100 - 150.
The achievements should get the following scores,
Achiev Score
<=80 = 1
85 = 25
90 = 50
95 = 75
100 = 100
105 = 125
>=110 = 150
Any Ideas ?
enter achievement score into column A, then in column B enter the formula below:
=IF(A1<=80,1,IF(A1<=85,25,IF(A1<=90,50,IF(A1<=95,75,IF(A1<=100,100,IF(A1<=105,125,IF(A1>105,150)))))))
Should work
Can you get a score of 96 for example?
If you can only get the scores you have listed you could set that up as a table and use a Vlookup to find the achievment and return the corresponding score.
Other way is to use a nested IF formula - If (Achieve<80, 1, (another nested IF)) - thats a bit messy though.
Does that help?
Quote from: mylestheslasher on February 18, 2011, 02:45:39 PM
If you can only get the scores you have listed you could set that up as a table and use a Vlookup to find the achievment and return the corresponding score.
Vlookup is the job for this problem. It will work with the ranges e.g. 85-90 gives 25, if they are in sorted order.
Thanks Lads for the suggestions ..
My problem is that I can have any value in between the values that I have suggested as well ..
Example, If I have a value of 87.6, I'll get a score of 38, I think ??, but I'm not sure how to come up with a formula for this ..
lads is there anywhere i can get excel downloaded for free or cheap for my home laptop???
Are there a limited number of scores - is it just as the first list suggests? It should be possible to nest seven IFs, otherwise use the Vlookup.
Your solution needs to be "two pronged" so:
1. Get the lower and upper range.
2. Get the average points increase per achievement increase.
3. Subtract the lower range from your score.
4. Multiply your score by the average points increase per achievement.
5. Add on the lower range points.
The above could maybe be done with vlookup and then formula on top of it. Not familiar enough with excel to know.
Quote from: mick999 on February 21, 2011, 11:47:08 AM
Thanks Lads for the suggestions ..
My problem is that I can have any value in between the values that I have suggested as well ..
Example, If I have a value of 87.6, I'll get a score of 38, I think ??, but I'm not sure how to come up with a formula for this ..
Provided A1 has a value then
=IF(A1<=80,1,IF(A1>=110,150,(A1-80)*5))
Will give you the correct value
Quote from: Banana Man on February 21, 2011, 11:49:27 AM
lads is there anywhere i can get excel downloaded for free or cheap for my home laptop???
Piratebay
Quote from: Maiden1 on February 21, 2011, 12:08:44 PM
Quote from: mick999 on February 21, 2011, 11:47:08 AM
Thanks Lads for the suggestions ..
My problem is that I can have any value in between the values that I have suggested as well ..
Example, If I have a value of 87.6, I'll get a score of 38, I think ??, but I'm not sure how to come up with a formula for this ..
Provided A1 has a value then
=IF(A1<=80,1,IF(A1>=110,150,(A1-80)*5))
Will give you the correct value
Thanks a Mill, That sorted it ..
Openoffice (http://www.openoffice.org) is effective enough for reading and using Excel files, and its free.
The other cheap source of MSOffice is a student licence, if you have any class of scholar in your dwelling.
Quote from: armaghniac on February 21, 2011, 04:09:27 PM
Openoffice (http://www.openoffice.org) is effective enough for reading and using Excel files, and its free.
The other cheap source of MSOffice is a student licence, if you have any class of scholar in your dwelling.
good man cheers
We recently moved to Office 2007 at work and today I used its conditional formatting function for the first time. Nice! It way easier to use than 2003 and it can automatically add colour codes, traffic lights etc to your data, as well as loads of cool stuff like filling the cell with a colour.
Excel is the business.
Is there a way to format numbers in excel so that
1
1.1
2
0.1
will be formatted exactly as listed above? Using the standard one decimal place gives
1.0
1.1
2.0
0.1
But I dont want to see things like "1.0"
Thanks in advance
Forget it. Just use General. How emmbarrassing!
QuoteHow emmbarrassing!
Twice in two quotes - keep up the good work. :P
I have a blind spot to the right of the Post button. I think it says "Spell Check", but I'm not sure.
Have been exporting fixtures from sevrasport onto excel.
im looking to break up a cell into 3 for a long list of fixtures, eg the cell would be "Dublin v Down". Im trying to make that 3 cells with in this example "dublin" in 1, "v" the next, while "down" the third.
Any help would be appreciated
Quote from: donelli on May 21, 2011, 11:52:52 PM
Have been exporting fixtures from sevrasport onto excel.
im looking to break up a cell into 3 for a long list of fixtures, eg the cell would be "Dublin v Down". Im trying to make that 3 cells with in this example "dublin" in 1, "v" the next, while "down" the third.
Any help would be appreciated
You could try Data>Text to Columns>Delimited by space.
Quote from: thebigfella on May 21, 2011, 11:57:49 PM
Quote from: donelli on May 21, 2011, 11:52:52 PM
Have been exporting fixtures from sevrasport onto excel.
im looking to break up a cell into 3 for a long list of fixtures, eg the cell would be "Dublin v Down". Im trying to make that 3 cells with in this example "dublin" in 1, "v" the next, while "down" the third.
Any help would be appreciated
You could try Data>Text to Columns>Delimited by space.
good man.
got that sorted and thanks for the prompt reply ;D
This problem has only arisen since we switched to Office 2007. In excel I have a large table of data with column filters switched on. I want to filter the list based on "xxx" being in a particular column and then delete all rows that contain"xxx". The filtering works fine but When I select all of the filtered rows and delete, it deletes everything in between the filtered rows as well. It's like a "visible cells only" issue but it never occured in 2003.
Help please.
Quote from: RMDrive on May 27, 2011, 11:08:39 AM
This problem has only arisen since we switched to Office 2007. In excel I have a large table of data with column filters switched on. I want to filter the list based on "xxx" being in a particular column and then delete all rows that contain"xxx". The filtering works fine but When I select all of the filtered rows and delete, it deletes everything in between the filtered rows as well. It's like a "visible cells only" issue but it never occured in 2003.
Help please.
Go to the data tab at the top and click re-apply on the sort & filter section (in the middle) then try and delete. I'm not sure if this works with deleting but with copying cells that are filtered sometimes it will select all cells and the way around it is to click the re-apply icon.
Alternatively sort the data by that column and delete those rows.
I have a spreadsheet with a column containing website names with hyperlinks. I want to sort and remove duplicates.
So I use the Data and Advanced Filter to copy Unique Records Only only to another column.
The copy and deduplication works okay, but I loose the hyperlink.
Is there a way of doing this and retain the hyperlink but without resorting to a macro?
What about inserting a column beside the column described.
Insert a formula =if b1=a1,"dupilcate". Copy this formula all the way down.
Sort column a in alphabetical order and it should group identicals on top of each other and display duplicate in the new column. then After a manual check sort column B (the new column you inserted) in alphabetical as well and all the duplicates should now be grouped together. Which you can now delete.
I would also insert a column in front of the one you listed and simply number it 1 to whatever all the way down. After all the sorting and deleting you can simply sort this column again in numerical from smallest to largest to return your data to the original look ( minus the duplicates).
Then delete the two inserted columns.
Just sorting, instead of filtering, preserves the hyperlinks. If you do that, all your duplicate records are grouped.
After sorting, say your data is in column A (from A2 down). In column B put a formula (from B2 down) like IF(A2=A1,0,B1+1).
Now all your duplicate records (except the first one of each set) have a zero opposite them. The wanted records have numerical values increasing from 1.
Now copy Column B and save it onto itself as Values (Copy, Paste Special, Values).
Sort by column B, ascending and all your duplicates are grouped at the top of column A and can be deleted as a block.
(Just saw Bensars's reply, but I'm posting this anyway, cos I can't stand the thought that my typing time was wasted).
Quote from: Hardy on July 18, 2011, 04:36:17 PM
(Just saw Bensars's reply, but I'm posting this anyway, cos I can't stand the thought that my typing time was wasted).
??? ...
lettuce/cosine I can't stand...
;)
Thanks Bensars. Emmm, yes I would have come up with that eventually too. It worked a treat.
Thanks also to Hardy - its the thought that counts.
Quote from: Fear ón Srath Bán on July 18, 2011, 04:41:36 PM
Quote from: Hardy on July 18, 2011, 04:36:17 PM
(Just saw Bensars's reply, but I'm posting this anyway, cos I can't stand the thought that my typing time was wasted).
??? ... lettuce/cosine I can't stand...
;)
Forgive me.
How do I add more columns (past IV) in Excel 2003?
The internet says 256 columns is max and I need to upgrade to 2007 or 2010. I don't like that answer.
I wouldn't think it's possible.
That's one mother of a spreadsheet.
Sounds like it could be unusable - i hope you're not planning to land it on some poor sod.
Quote from: Orior on July 19, 2011, 04:36:05 PM
That's one mother of a spreadsheet.
Sounds like it could be unusable - i hope you're not planning to land it on some poor sod.
Thankfully I'm not involved. Girl in my team is preparing data for data migration but in hindsight has made a hash of her spreadsheet as it has more columns than rows and has hit a wall at IV! If she did it the other way around she would have 200 columns and could then add rows to the cows came home. Ah well!
Quote from: Tony Baloney on July 19, 2011, 05:19:27 PM
Quote from: Orior on July 19, 2011, 04:36:05 PM
That's one mother of a spreadsheet.
Sounds like it could be unusable - i hope you're not planning to land it on some poor sod.
Thankfully I'm not involved. Girl in my team is preparing data for data migration but in hindsight has made a hash of her spreadsheet as it has more columns than rows and has hit a wall at IV! If she did it the other way around she would have 200 columns and could then add rows to the cows came home. Ah well!
get her to do it again or can she not transpose the data?
Yes - she should be able to transpose the data.
Yes, copy the data, then Paste Special and ensure Transpose option is ticked.
Quote from: saffron sam2 on July 19, 2011, 08:57:13 PM
Yes, copy the data, then Paste Special and ensure Transpose option is ticked.
I wish PhotoShop had a similar feature. Then we could copy pictures from the not safe for work thread, and paste with the Undress opton ticked.
Cheers lads just getting this now. Will get her to check it out tomorrow.
Quote from: Mac Eoghain on August 15, 2011, 08:17:34 PM
Any experts on at the minute in using INDEX(), MATCH() or LOOKUPs of various guises. This b***ard is going to beat me.
I didnt think there is much anyone can teach you boyo. The only tip I can offer with Lookups is to make sure your lookups are sorted.
Need help. I am currently running microsoft office 2010.
I need to add a conditional format on cells that if they are over 15 characters the cell will change colour.
Currently I am trying to add a new rule through conditional format:
=Len(A1)>15
format fill cell red.
However this is not working :-\
Ideas please?
Quote from: Hashtag on October 20, 2011, 09:39:10 AM
Need help. I am currently running microsoft office 2010.
I need to add a conditional format on cells that if they are over 15 characters the cell will change colour.
Currently I am trying to add a new rule through conditional format:
=Len(A1)>15
format fill cell red.
However this is not working :-\
Ideas please?
Got it sorted. =Len(A1)>=15
Hi folks, can anyone advise me on a formula or a quick method on how i would turn the below info into whole numbers ?
Been playing about with RIGHT and LEFT function but no joy as of yet.
E.g. I would want the top line to be showing as 24 instead of d24:h21:m44:s27.
I would want the second line to be showing as 17 instead of d17:h02:m59:s50.
If anyone could help at all it would be very much appreciated.
cumulativeAge
d24:h21:m44:s27.
d17:h02:m59:s50.
d10:h04:m51:s19.
d08:h04:m28:s51.
d08:h04:m03:s06.
d07:h04:m47:s07.
d07:h04:m45:s04.
d06:h12:m56:s43.
d03:h05:m06:s02.
d02:h20:m57:s23.
d02:h19:m57:s08.
d02:h14:m19:s03.
=MID(A1,2,2)
I'd have thought you'd need to get the cell into a number format and ROUND.
If you don't mind having extra columns you can highlight all the cells that you want to format then use
Data>Text to Columns>
Then have the delimiter as :
Will create 4 columns as below, highlight all these rows and you can do find and replace for all the letters then.
d24 h21 m44 s27.
d17 h02 m59 s50.
d10 h04 m51 s19.
d08 h04 m28 s51.
d08 h04 m03 s06.
d07 h04 m47 s07.
d07 h04 m45 s04.
d06 h12 m56 s43.
d03 h05 m06 s02.
d02 h20 m57 s23.
d02 h19 m57 s08.
d02 h14 m19 s03.
You could always just do mid like above though ::)
Thanks a lot gents, appreciate your help.
Hi there,
A question on Vlookups
I am just doing a VLOOKUP on a particular file as below ..
=VLOOKUP(B18,'[Costs_2011_12_11.XLSX]CDB_Output'!$A$2:$D$65536,4,FALSE)
My Costs file has 80,000 rows and if I go above 65536, I get an invalid reference, that I cannot reference Rows beyond 65536 ..
Is there any way around this ??
Cheers ..
Recent versions of Excel can have more than 65536 rows. However, I would not do big operations like this in Excel. At least use MSAccess.
Quote from: Mac Eoghain on December 12, 2011, 04:12:43 PM
Mick999 - have you tried splitting the costs file into 2 files - then do the vlookup across the 2 workbooks?
Hi Mac
I'll try that ..
just wondered was there some other way .. of seperating the vlookup into a before and after 65536 type scenario, but I've been trying and it doesn't seem to work either ..
I'm using Excel 2007, Does anyone know which version works with this No, Of rows ?
Thanks,
Mick
Quote from: mick999 on December 12, 2011, 09:16:59 PM
Quote from: Mac Eoghain on December 12, 2011, 04:12:43 PM
Mick999 - have you tried splitting the costs file into 2 files - then do the vlookup across the 2 workbooks?
Hi Mac
I'll try that ..
just wondered was there some other way .. of seperating the vlookup into a before and after 65536 type scenario, but I've been trying and it doesn't seem to work either ..
I'm using Excel 2007, Does anyone know which version works with this No, Of rows ?
Thanks,
Mick
Are you doing the lookup in a separate file?
If so, save that file as 2007 and the lookup should work. Do the lookup between columns a:d i.e. remove the row numbers
I have numerous excel files with a column of data in each. Each column has over 10,000 entries in it. These entries correspond to how active or not the case subject was every minute (it's a study of the benefits of exercise in cancer research). The entries range from 0 up to 2000 for every minute of every day for a week. I need to extract how many times each subject undergone vigorous activity for a period of more than 10 minutes everyday. Vigorous activity equals a number greater than 984. i.e. I need to extract how many consecutive entries of 10 or more are greater than 984. In the below example the output would be 15.
352
277
921
74
1988
2153
3027
2364
2568
3923
3745
3122
3225
3529
3175
2941
3721
3298
3678
603
63
560
507
134
0
167
Now to make a difficult assignment even more tedious there is one exception I need to look out for. These consecutive entries of 10 or more greater than 984 can be separated by a gap of one entry (less than 984) in every 5 entries, i.e. at least five consecutive entries greater than 984 can be followed by one entry less than 984 where it is in turn followed by at least five consecutive entries greater than 984. In the below example the output would be 13.
277
921
74
1988
2153
3027
2364
2568
603
3678
3475
3204
2962
3982
2653
2236
1425
507
134
0
167
I hope that is somewhat clear and the gaaboard gods are smiling. It takes me about 15 minutes to search through each file and I've worked out it will take me 75 hours to sort through all the files I have. HELP!!! :'(
define 'numerous' amount of files...10, 100, 1000?
also, are they csv or xls?
Your first job is to get them all on the same sheet. Should be simple enough after that. A VBA macro will probably work to achieve first part. A quick google or two will throw you up one of those. Then write a formula to calculate what you want for one column and expand that out to all.
Get SPSS
This should work, but test it – there could be a combination of data I haven't thought of.
Put your data in column A, starting at, say, Row 10.
In cell B10: =IF(A10>984,B9+1,0) - counts consecutive entries >984.
In cell C10: =IF(B10>=5,IF(B11=0,IF(B12>0,"x",""),""),"") – marks a gap after five consecutive entries >984.
In cell D10: =IF(B10>9,IF(B11=0,1,0),0) – counts instances of your first criterion.
In cell E10: =IF(C4="x",IF(B10>=5,1,0),0) – counts instances of your second criterion.
Of course, copy A10:E10 all the way down to the end of your data.
In some cell, put =SUM(D10:E65536) – the total number of occurrences.
(I'm assuming that ten OR MORE consecutive entries >984 counts as one occurrence - i.e. 19 consecutive >984 is still just one occurrence of your criterion 1 and likewise for criterion 2: 5+5 and 5+13 are each one occurrence.)
[Edit - sorry - you need to start the data column further down than Row 2, as I originally had it, to allow for checking back six rows. I've made the changes above]
I was thinking something like above ::) or you could write a macro something like below to do a max count if all the values where always in column A.
Private Sub cmdCount_Click()
Dim intCurrentCount As Integer
Dim intCurrentRow As Integer
Dim intCountConsecutive As Integer
Dim intMaxCount As Integer
Dim blnFound As Boolean
blnFound = True
intCurrentCount = 0
intCurrentRow = 0
intMaxCount = 0
While blnFound
intCurrentRow = intCurrentRow + 1
If Cells(intCurrentRow, 1) = "" Then
blnFound = False
Else
If Cells(intCurrentRow, 1) > 984 Then
intCurrentCount = intCurrentCount + 1
intCountConsecutive = intCountConsecutive + 1
Else
If intCountConsecutive < 5 Then
If intMaxCount < intCurrentCount Then
intMaxCount = intCurrentCount
End If
intCurrentCount = 0
End If
intCountConsecutive = 0
End If
End If
Wend
Cells(1, 3) = intMaxCount
End Sub
Thanks lads. Was thinking of something like Hardy's but macros might be what I'm really after, excel skills are poor to never used, thanks for the idea and the work Maiden.
Did a bit of coding in college so just stepping through your code am I right in thinking that will just return the maximum number of consecutive rows that are greater than 984?
I need to record every instance where there is at least 10 consecutive entries great than 984 (remember there can be a gap of one entry less that 984 after at least 5 consecutive entries as long as it is followed by another 5 consecutive entries greater than 984). Ideally row C will store every instance of these consectutive entries so, for example, if you take the instance of all the numbers in my previous post the output would be:
15
13
I suppose I can use something like the below to save each consecutive count number once it reaches two entries less than 984?
intSaveRow + 1
Cells(intSaveRow , 3) = intCountConsecutive
Quote from: Croí na hÉireann on February 28, 2012, 04:31:55 PM
Thanks lads. Was thinking of something like Hardy's but macros might be what I'm really after, excel skills are poor to never used, thanks for the idea and the work Maiden.
Did a bit of coding in college so just stepping through your code am I right in thinking that will just return the maximum number of consecutive rows that are greater than 984?
I need to record every instance where there is at least 10 consecutive entries great than 984 (remember there can be a gap of one entry less that 984 after at least 5 consecutive entries as long as it is followed by another 5 consecutive entries greater than 984). Ideally row C will store every instance of these consectutive entries so, for example, if you take the instance of all the numbers in my previous post the output would be:
15
13
I suppose I can use something like the below to save each consecutive count number once it reaches two entries less than 984?
intSaveRow + 1
Cells(intSaveRow , 3) = intCountConsecutive
Yes that is correct it only records the maximum in row 1,3
If you want to record everytime it > 15 then
Where it has
If intMaxCount < intCurrentCount Then
intMaxCount = intCurrentCount
End If
Change that to
If intCurrentCount >= 15 Then
Cells(intCurrentRow - 2, 3) = intCurrentCount
End If
That works maiden, getting my head around it now, thanks again. Where's the applause smile??? This one will have to do :-*
:P
Quote from: Croí na hÉireann on March 01, 2012, 04:40:51 PM
That works maiden, getting my head around it now, thanks again. Where's the applause smile??? This one will have to do :-*
:P
haha good stuff :)
Bascially I have a table of information like below
Product Description Size
X1 Thing 1 1
X2 Thing 2 1
X3 Thing 3 1.5
I have created a drop down list so that I can choose the product- then I want the other information to be automatcially pulled into the next cells- ie description, size
So if I chose X2 from my drop down list- the description automatically pulled in would be Thing 1 and the Size would be 1.
I'd say use an IF function with LOOKUP for lists on another page or hidden on this page.
Something like below should work if the values you are looking for are in Sheet2 in top left corner.
Then if you enter X3 in Cell A:1 in Sheet1
and have below in cell B:1
=IF(ISNA(VLOOKUP(A1,Sheet2!$A1:$C3,2,FALSE)),"",VLOOKUP(A1,Sheet2!$A1:$C3,2,FALSE))
Cell B:1 would have value 'Thing 3'.
There may be better ways of populating the cells.
Back again, if I had 100 files, named File001 to File100 and I wanted to extract a row of data out of each of them and write these rows to individual new files how would I go about it? I'm thinking strings with a counter but which string function would you use?
I have a excel sheet where i am trying to create a value in a cell by multiplying it by another but I only want the value if the one cell is over a certain value if it is not I want the calculation to equal zero.
Eg.
Cell A = Anything between 1-16
Cell B = 3.68
Cell C = A x B (I want this to be zero)
However if it is:
Cell A = Anything over 16
Cell B = 3.68
Cell C = A x B (I want this to be the value e.g. 17x3.68=62.56)
Can anyone help?
Quote from: Mac Eoghain on March 27, 2012, 11:04:33 AM
Quote from: Radda bout yeee on March 27, 2012, 10:28:22 AM
I have a excel sheet where i am trying to create a value in a cell by multiplying it by another but I only want the value if the one cell is over a certain value if it is not I want the calculation to equal zero.
Eg.
Cell A = Anything between 1-16
Cell B = 3.68
Cell C = A x B (I want this to be zero)
However if it is:
Cell A = Anything over 16
Cell B = 3.68
Cell C = A x B (I want this to be the value e.g. 17x3.68=62.56)
Can anyone help?
In Cell C1:
=IF(A1>16,(A1*B1),0)
Thanks for that - only just realising If it is over 16 I want the value to be the amount over 16 if that makes sense:
E.g.
Cell A = Anything over 16
Cell B = 3.68
Cell C = A x B (I want this to be the value e.g. 17x3.68=3.68 or 27x3.68=40.48)
I have an Excel spreadsheet where I need to load a demand over a number of months as follows (numbers in red to be calculated rather than manually entered):
Type Start Rate Max Jan Feb Mar Apr May Jun Jul Aug
A Feb 3 9 0 3 3 3 0 0 0 0
B Apr 2 8 0 0 0 2 2 2 2 0
I have the basics in that I added a column to calculate the total months (Max / Rate) and added this to the start month to give me the End point. I then put an If (Month>=start and Month<End, rate,0) and this gives me my demand per month. However this only works where the Max/Rate is a whole number.
For example if the rate was 3 and the Max was 10 my formula would give me
C Feb 3 10 0 3 3 3 3 0 0 0
Is there any way to get Excel to load the demand so that only the balance goes into the final month rather than the full amount?
D Feb 3 10 0 3 3 3 1 0 0 0
Hope this makes sense :-[
QuoteIs there any way to get Excel to load the demand so that only the balance goes into the final month rather than the full amount?
count the demand left to be fulfilled and use a MIN function
something like
=MIN(TRUNC($A$3/$B$3),$A3-SUM($C$3:E3)) should work
Trying to analyse some survey results. Small survey so was going to do it myself through excel. I have no problem coding the straight closed ended multiple choice questions, and the open ended qualitative questions (survey has a mixture of both) I'm having some difficulty however coding a closed ended question with a yes, no, don't know response box followed by a related open ended question that reads 'if the answer to the above question is no, can you explain in your own words why not.'
Two questions:
1- Should this be treated as one question when coding?
2- How would this be represented in order to create frequencies and pivot table cross tabs in excel?
Please treat me as a child if responding, my knowledge of excel is minimal to say the least. :o
Quote from: armaghniac on July 24, 2012, 05:04:59 PM
QuoteIs there any way to get Excel to load the demand so that only the balance goes into the final month rather than the full amount?
count the demand left to be fulfilled and use a MIN function
something like
=MIN(TRUNC($A$3/$B$3),$A3-SUM($C$3:E3)) should work
Thanks, I will play about with this. My existing formula is =IF(AND($B2<=L$1,$F2>L$1),$D2,0) so it is the $D2 I need to replace with this additional function.
EDIT: It worded alright, though I didn't need the Truncation. Cheers.
=IF(AND($B2<=J$1,J$1<$F2),MIN($D2,$C2-SUM($H2:I2)),0)
I'm running Excel 2007 on Windows XP.
When I double click on a spreadsheet file, Excel will open, but the document will not appear until I Alt&Tab away then Alt&Tab back again. Google hasnt helped.
Any ideas?
i am trying to count the number of occurance a certain date appear in a column but only when it is occurs on the planned date
on the wee table below i want folmula to show me how many times the 10/09/2012 occurs beside planned ( so that it doesnt count the 10/09/2012 beside actual)
Planned 10/09/2012
Actual 10/09/2012
Planned 10/09/2012
Actual 17/09/2012
Planned 10/09/2012
Actual 16/09/2012
Planned 17/09/2012
Actual 17/09/2012
that isa work of art, works perfectly with 'planned' but i try the exact same formula and range it wont work if i try 'actual'.
i would have thought by replacing the word and leaving everything else the forlula would work?
Didnt see your reply here Mac, thanks for your help, i have it working but will have to adapt it soon so i might be needing your help again! Ill drop you a line then cheers.
This seems basic but for some reason I cannot get the correct values, some help PLEASE?
Please show the formulas you would use to solve this in Microsoft Excel? You need to calculate the selling price of the item and the total price for the sale. Cell A1 = item cost, Cell B1 = quantity to be sold, Cell C1 = mark up of the item over cost
It is driving me nuts, maybe not enough sleep.
Thanks,
DH
Quote from: DrinkingHarp on January 26, 2013, 06:49:50 AM
This seems basic but for some reason I cannot get the correct values, some help PLEASE?
Please show the formulas you would use to solve this in Microsoft Excel? You need to calculate the selling price of the item and the total price for the sale. Cell A1 = item cost, Cell B1 = quantity to be sold, Cell C1 = mark up of the item over cost
It is driving me nuts, maybe not enough sleep.
Thanks,
DH
Selling Price = A1*C1
Total value of transaction = A1*B1*C1
Wrong, unless you think C1 is a % and it is probably still wrong
Quote
Wrong, unless you think C1 is a % and it is probably still wrong
For the above to work C would have to a 100%+the markup, i.e. selling price expressed as 150% of cost price, not 50% markup. Otherwise A1*B1*(1+C1) is needed where C1 is expressed as fraction or percentage.
If markup is a fixed amount, e.g 50c more then it would be something like (A1+C1) would be the price and then multiply by the quantity.
Ok, back here again
I have a large PDF document ( a snag list) with approx 400 comments, each with an adjoining photo. I want to export this to excel so i can format and be able to sort/organise. I have Acrobat X so have no problem getting the text to excel but cannot find an easy way of getting the photos
Quote from: ludermor on February 22, 2013, 11:10:25 AM
Ok, back here again
I have a large PDF document ( a snag list) with approx 400 comments, each with an adjoining photo. I want to export this to excel so i can format and be able to sort/organise. I have Acrobat X so have no problem getting the text to excel but cannot find an easy way of getting the photos
Not sure about what is the best way but I think you may have to use a tool like irfanview to open the pdf, crop out the picture you want and then manually insert into excel. I am not sure there is a nice quick way to do it.
cheers, that got all the photos on the hard drive but doesn't let me put onto the excel sheet, unless im missing something :)
You have to manually insert I presume using insert, picture...
tried that but it wont let me sort, the picture wont stay in the cell.
cheers , that works for a small sample but i have over 500 notes with 500 pictures! it looks like i will have to put each picture in individually !
Do you get CPD points for loitering on this thread?
A quick one for the excel experts. Column A is a list of invoices numbers and Column B is the value of the invoices. The numbers in Column A are not ascending numerically as they should be. I can filter them to get them to ascend numerically but the values in Column B will not move accordingly. What is the solution?
Select all the rows, go to sort button. Sort by column a. Column b will move with it.
Thanks for that, something so simple. :-[
Folks, anyone with a spread sheet with a formula for calculating depreciation of fixed assets for montly management accounts? I have been trying, but cant seem to crack it.
all help appreciated
Surely it's simple to construct your own spreadsheet to do that.Asset description,value,rate of monthly depreciation with formula to calculate?
Quote from: T Fearon on September 27, 2013, 07:05:16 AM
Surely it's simple to construct your own spreadsheet to do that.Asset description,value,rate of monthly depreciation with formula to calculate?
Yes you tell him Tony.
Why would anyone come on here and ask how to do something, when TF already knows how to do it?
Try this.
Put some values in four columns. In the fifth column, sum the first three columns.
A1=1
B1=1
C1=1
D1=5
E1=Sum(A1:C1) = 3
Now delete the value in D1 and replace it with some other value.
See what happens to E1?
This happens in Excel 2007. I don't know about other versions. It cost my colleague two days of debugging in some VBA code he was doing this week.
Quote from: Hardy on May 24, 2014, 01:00:55 PM
Try this.
Put some values in four columns. In the fifth column, sum the first three columns.
A1=1
B1=1
C1=1
D1=5
E1=Sum(A1:C1) = 3
Now delete the value in D1 and replace it with some other value.
See what happens to E1?
This happens in Excel 2007. I don't know about other versions. It cost my colleague two days of debugging in some VBA code he was doing this week.
This doesn't present a problem for me in Excel 2010.
OK.
Actually Hardy I read your instructions again, I was changing the value in D1 not deleting it. When I delete D1 and enter a new value I do get a problem! Libreoffice does not behave like this. That's a bit odd.
Yeah - it's the usual stuff of systems trying to be too smart. When you delete the value in the column not included in the sum it resets its assumptions and decides you'd want to sum all columns in the range up to the result column.
Change the code in E1 to =SUM($A$1:$A$3) . You can change other cells and it won't change the range.
Quote from: Maiden1 on May 24, 2014, 02:57:18 PM
Change the code in E1 to =SUM($A$1:$A$3) . You can change other cells and it won't change the range.
you can do this, but in a spreadsheet you would probably have wanted to copy the formula.
Using the "everything up to current row/column" is a reasonable default for an initial entry for SUM, you can always change it.
But existing formulas changing because of the alteration of cells not in the range covered by the formula is not on.
Quote from: armaghniac on May 24, 2014, 03:04:46 PM
Quote from: Maiden1 on May 24, 2014, 02:57:18 PM
Change the code in E1 to =SUM($A$1:$A$3) . You can change other cells and it won't change the range.
you can do this, but in a spreadsheet you would probably have wanted to copy the formula.
Using the "everything up to current row/column" is a reasonable default for SUM, you can always change it.
But formulas changing because of the alteration of cells not in the range covered by the formula is not on.
Definitely strange it does that. Another way to do the formula is below where it offsets by 2 cells. You can copy the formula using this.
=SUM(A1:OFFSET(E1,0,-2))
No version of excel likes you to do calculations in cells not adjacent to the cell involved, for some reason. Not entirely sure why - actually I don't have the first idea.
In Excel 2007, something weird happens when I try to do a Find and Replace in a range of COUNTIF formulae. Can anyone try the setup below and see if it happens and let me know the Excel version you're using? Also, any insight would be appreciated.
(http://i648.photobucket.com/albums/uu206/Hardyarse/ExcelCountif_zpsa8439ec0.png) (http://s648.photobucket.com/user/Hardyarse/media/ExcelCountif_zpsa8439ec0.png.html)
Column C is counting the number of instances of each category in the range A1 to A5. Now I want to count the number of instances of each category in the range A6 to A10. So I do a Find and Replace on the formula range C1 to C3. I put $1 in the Find box and $6 in the Replace box and hit Replace All.
Tell me what happens in your case.
Quote from: Hardy on September 24, 2014, 11:28:32 AM
In Excel 2007, something weird happens when I try to do a Find and Replace in a range of COUNTIF formulae. Can anyone try the setup below and see if it happens and let me know the Excel version you're using? Also, any insight would be appreciated.
(http://i648.photobucket.com/albums/uu206/Hardyarse/ExcelCountif_zpsa8439ec0.png) (http://s648.photobucket.com/user/Hardyarse/media/ExcelCountif_zpsa8439ec0.png.html)
Column C is counting the number of instances of each category in the range A1 to A5. Now I want to count the number of instances of each category in the range A6 to A10. So I do a Find and Replace on the formula range C1 to C3. I put $1 in the Find box and $6 in the Replace box and hit Replace All.
Tell me what happens in your case.
Replaced $1 with $6 and it updated to the following - also using Excel 2007.
=COUNTIF(A$5:A$6,...
Tried:
Find: A$1:A$5
Replace with: A$6:A$10
That worked ok - not sure why the initial find and replace does that
That's what I'm getting too. I can't understand what's happening in the first case. Are you using 2007 version too?
BAsic one here - how do you input numbers to a cell (e.g. phone nbrs) without losing the 0 at the beginning?
Quote from: DuffleKing on September 24, 2014, 01:02:56 PM
BAsic one here - how do you input numbers to a cell (e.g. phone nbrs) without losing the 0 at the beginning?
Format the column or whatever as Text before you type them in.
or for individual entries a quote will signal that you want text e.g. '02830881122
Quote from: Hardy on September 24, 2014, 12:48:26 PM
That's what I'm getting too. I can't understand what's happening in the first case. Are you using 2007 version too?
I said in my earlier post I was using 2007 - it's actually 2010
Sorry anyway. So it's either a bug that was never seen and so carried into the 2010 version, or it's not a bug, in which case I can't see what it's meant to achieve.
I have to admit I do like finding out new things in Excel and there's not much better than a perfectly formulated spreadsheet but to normal people is this possibly the most boring thread on the board??
Anyone been on an excel course recently ? Im being asked to go on a basic one but i did basic and advanced about 10 years ago. Has it changed much in the interim ? Just dont want to waste my time if its going to be the same.
Quote from: take_yer_points on September 24, 2014, 12:08:58 PM
Quote from: Hardy on September 24, 2014, 11:28:32 AM
In Excel 2007, something weird happens when I try to do a Find and Replace in a range of COUNTIF formulae. Can anyone try the setup below and see if it happens and let me know the Excel version you're using? Also, any insight would be appreciated.
(http://i648.photobucket.com/albums/uu206/Hardyarse/ExcelCountif_zpsa8439ec0.png) (http://s648.photobucket.com/user/Hardyarse/media/ExcelCountif_zpsa8439ec0.png.html)
Column C is counting the number of instances of each category in the range A1 to A5. Now I want to count the number of instances of each category in the range A6 to A10. So I do a Find and Replace on the formula range C1 to C3. I put $1 in the Find box and $6 in the Replace box and hit Replace All.
Tell me what happens in your case.
Replaced $1 with $6 and it updated to the following - also using Excel 2007.
=COUNTIF(A$5:A$6,...
Tried:
Find: A$1:A$5
Replace with: A$6:A$10
That worked ok - not sure why the initial find and replace does that
I don't see what the problem is?
When you replace $1 with $6, the formula would read =countif(A$6:A$5,.......). But as 5 < 6 excel automatically puts the formula in chronological order, i.e. A$5:A$6.
If you then replace $5 with $10 you get the formula =countif(A$6:A$10........) as desired.
Maybe I'm misunderstanding something but I don't see what the problem is?
Westbound, I didn't realise that's what it was doing, because I didn't go beyond the first step and wondering, "what the hell's going on?". To get the job I was doing done, I just edited the whole string.
Anyway, you're right - that's what it's doing. Thanks for pointing it out. What's wrong with it, in my view, is that it's completely counter-intuitive. It was for me, anyway. It would never have occurred to me, after the first step, when I now had "A$5:A$10" and was scratching my head, that replacing 5 with 10 would give me "A$6:A$10". I don't know any other formulae that respond this way to Find and Replace (maybe there are), but it's completely at odds with how I expect the Find/Replace function to work - i.e. as a text editor.
It has worked as a text editor though. You've changed the first cell in your range from A1 to A6 but you haven't changed the second cell, which is what westbound has said. You're looking for find/replace to move the second cell in the range relative to the first cell, which I don't think it can do
Sorry, I think I'm missing the point. I always thought Find and Replace simply treated the content of any sell as text, even if it was a formula.
Say I have 1 in A1, 2 in A2 and 3 in A3. In B1 I have the formula =A1+A2 and the result is 3. Now if I select column B and go "Find A1, Replace with A3", my formula in B1 changes to =A3+A2 and the result is 5. It has simply edited the text in the formula.
In my problem formula, I was using Find and Replace to edit the text in the whole range of formulae to change A1 to A6. I was then going to do a subsequent Find and Replace to change A6 to A10. The last thing I expected a text editor to do was Change A1 to A5 when I told it to change it to A6. Even less did I expect it, if I had then told it to change A5 to A10 (which I didn't, since I now had A5:A5 as the formula text and it wouldn't make sense to change it to A10:A10) to find a non-existent A6 and change it to A10.
Am I making sense?
Not really but it might be me that's confused!
Original range: A1:A5
Find A1 / Replace A6
Range is now A5:A6
Find A5 / Replace A10
Range is now A6:A10
QuoteSorry, I think I'm missing the point. I always thought Find and Replace simply treated the content of any sell as text, even if it was a formula.
The Find and Replace does treat the content as text, but the formatting of that text then determines how it is displayed, in this case the representation of formulas.
A bit like Duffleking's problem with the phone numbers. If you had of numbes 3531222333 etc and you changed these to +35312223333 then the replace would change them but you'd still end up with the original number.
Quote from: Hardy on September 24, 2014, 05:23:08 PM
In my problem formula, I was using Find and Replace to edit the text in the whole range of formulae to change A1 to A6. I was then going to do a subsequent Find and Replace to change A6 to A10. The last thing I expected a text editor to do was Change A1 to A5 when I told it to change it to A6. Even less did I expect it, if I had then told it to change A5 to A10 (which I didn't, since I now had A5:A5 as the formula text and it wouldn't make sense to change it to A10:A10) to find a non-existent A6 and change it to A10.
Am I making sense?
Not really making sense to me!
Couple of things;
1 - the subsequent Find and Replace should be to change A5 to A10. (not A6 to A10).
2 - it didn't Change A1 to A5. A5 is still in the formula (it just appears before A6 in the formula). A1 was replaced by A6, and A5 remained in the formula. You now have both A6 and A5 in the formula, but they are in the other order.
3 - You shouldn't have A5:A5 as the formula text at this stage. It should be A5 to A6.
The countif formula in excel is designed that the range must be in chronological order. To prove this point, type the following into an empty cell
=countif(A7:A4,B1)
When you hit enter and look back at what you have typed, excel has automatically corrected the formula to read as follows:
=countif(
A4:A7,B1)
Hope this makes sense?
It does make sense (same for laceer, armaghniac). Clearly, this is how Excel works. And it would have worked fine if I'd gone ahead and done my two-stage Find/Replace without noticing at the first stage that it had seemed to do something other than what I'd asked.
I presume there are lots of other Excel formulae that work this way - I just never noticed it before.
Possibly something covered before, but if you enter a 16 digit number into excel, then it will round it to the first 15 digits. B'astardo.
Even if you clever store it as text by preceding it with a single apostophe, then convert it back to a number using the value function it rounds calculations. B'astardo.
Does Openoffice work any better?
Open office works the same way
Quote from: Orior on November 26, 2014, 03:24:37 PM
Possibly something covered before, but if you enter a 16 digit number into excel, then it will round it to the first 15 digits. B'astardo.
Even if you clever store it as text by preceding it with a single apostophe, then convert it back to a number using the value function it rounds calculations. B'astardo.
Does Openoffice work any better?
We don't have as much money to add up as you, Orior, so are less familiar with this problem.
But presumably the spreadsheets are storing cells as double precision floating point number, so this has only 15 decimal digits.
You might need something like this http://precisioncalc.com/What_is_xlPrecision.html
Quote from: armaghniac on November 26, 2014, 04:22:08 PM
Quote from: Orior on November 26, 2014, 03:24:37 PM
Possibly something covered before, but if you enter a 16 digit number into excel, then it will round it to the first 15 digits. B'astardo.
Even if you clever store it as text by preceding it with a single apostophe, then convert it back to a number using the value function it rounds calculations. B'astardo.
Does Openoffice work any better?
We don't have as much money to add up as you, Orior, so are less familiar with this problem.
But presumably the spreadsheets are storing cells as double precision floating point number, so this has only 15 decimal digits.
You might need something like this http://precisioncalc.com/What_is_xlPrecision.html
That reminds me about the scientist from university who was talking to a ship building welder in the pub:
Scientist: "You know, in my job we work to one hundredth thousand of a millimetre"
Welder: "Oh that's no good where I work. We have to be dead on"
Quote from: Orior on November 26, 2014, 04:33:06 PM
That reminds me about the scientist from university who was talking to a ship building welder in the pub:
Scientist: "You know, in my job we work to one hundredth thousand of a millimetre"
Welder: "Oh that's no good where I work. We have to be dead on"
Dead on joke.
Probably more algerbra than excel question!
im trying to find out actual wages from one of my subcontractor and he has 3 different pay grades for trades. i have a total for the months and % allocation of the trades. Im sure there is an easy way to find out but my math brain has left me!
Month 1 total cost = 4972 which is made up of Tom x 33% , John x 12%, Mick x 30%
Month 2 Total cost = 6499 which is made up of Tom x 50% , John x 10%, Mick x 34%
How much is the monthly cost of each person?
Do you mean this?
(http://s22.postimg.org/m7yhqrpr5/Costs.jpg) (http://postimage.org/)
image upload no limit (http://postimage.org/)
As far as I can see, John is hanging the latch.
And Tom is swinging the lead.
Quote from: Hardy on February 18, 2015, 03:43:33 PM
As far as I can see, John is hanging the latch.
Must be the YTP apprentice!
Quote from: WeeDonns on February 18, 2015, 02:59:41 PM
Do you mean this?
(http://s22.postimg.org/m7yhqrpr5/Costs.jpg) (http://postimage.org/)
image upload no limit (http://postimage.org/)
No the % of each worker is the % of their wages, not the % of the monthly cost. I'm trying to find out their individual monthly cost.
In that case do you have enough information? You'd be looking to set up simultaneous equations but would need a third one?
I'm not sure Gallsman, that's why I'm asking here!
Quote from: ludermor on February 18, 2015, 05:19:55 PM
I'm not sure Gallsman, that's why I'm asking here!
I'm having horrible flashbacks of GCSE and A Level work on matrices. Off the top of my head, I don't think you've enough info - you've three unknowns but only two equations.
Quote from: ludermor on February 18, 2015, 02:50:52 PM
Probably more algerbra than excel question!
im trying to find out actual wages from one of my subcontractor and he has 3 different pay grades for trades. i have a total for the months and % allocation of the trades. Im sure there is an easy way to find out but my math brain has left me!
Month 1 total cost = 4972 which is made up of Tom x 33% , John x 12%, Mick x 30%
Month 2 Total cost = 6499 which is made up of Tom x 50% , John x 10%, Mick x 34%
How much is the monthly cost of each person?
Ask the subcontractor to tell you! Simples!
I might be missing something here, but your percentages dont add up to 100%.
Quote from: ludermor on February 18, 2015, 02:50:52 PM
Probably more algerbra than excel question!
im trying to find out actual wages from one of my subcontractor and he has 3 different pay grades for trades. i have a total for the months and % allocation of the trades. Im sure there is an easy way to find out but my math brain has left me!
Month 1 total cost = 4972 which is made up of Tom x 33% , John x 12%, Mick x 30%
Month 2 Total cost = 6499 which is made up of Tom x 50% , John x 10%, Mick x 34%
How much is the monthly cost of each person?
If I understand you correctly, you're short one piece of information. You have two equations:
4972 = 0.33T + 0.12J + 0.3M
6499 = 0.5T + 0.1J +0.34M
You need some relationship to create a 3rd equation if you're looking for an exact answer. You could get a decent estimate from the info you currently have but I presume that's no good?
Quote from: macdanger2 on February 18, 2015, 06:48:41 PM
Quote from: ludermor on February 18, 2015, 02:50:52 PM
Probably more algerbra than excel question!
im trying to find out actual wages from one of my subcontractor and he has 3 different pay grades for trades. i have a total for the months and % allocation of the trades. Im sure there is an easy way to find out but my math brain has left me!
Month 1 total cost = 4972 which is made up of Tom x 33% , John x 12%, Mick x 30%
Month 2 Total cost = 6499 which is made up of Tom x 50% , John x 10%, Mick x 34%
How much is the monthly cost of each person?
If I understand you correctly, you're short one piece of information. You have two equations:
4972 = 0.33T + 0.12J + 0.3M
6499 = 0.5T + 0.1J +0.34M
You need some relationship to create a 3rd equation if you're looking for an exact answer. You could get a decent estimate from the info you currently have but I presume that's no good?
Yeah thats right Macdanger, if i knew one ofthe wages i could work out the rest but im struggling.
Quote from: ludermor on February 18, 2015, 07:02:46 PM
Quote from: macdanger2 on February 18, 2015, 06:48:41 PM
Quote from: ludermor on February 18, 2015, 02:50:52 PM
Probably more algerbra than excel question!
im trying to find out actual wages from one of my subcontractor and he has 3 different pay grades for trades. i have a total for the months and % allocation of the trades. Im sure there is an easy way to find out but my math brain has left me!
Month 1 total cost = 4972 which is made up of Tom x 33% , John x 12%, Mick x 30%
Month 2 Total cost = 6499 which is made up of Tom x 50% , John x 10%, Mick x 34%
How much is the monthly cost of each person?
If I understand you correctly, you're short one piece of information. You have two equations:
4972 = 0.33T + 0.12J + 0.3M
6499 = 0.5T + 0.1J +0.34M
You need some relationship to create a 3rd equation if you're looking for an exact answer. You could get a decent estimate from the info you currently have but I presume that's no good?
Yeah thats right Macdanger, if i knew one ofthe wages i could work out the rest but im struggling.
Is this a maths problem or a real world problem ludermor? If it's a maths problem, there must have been something else provided
No its a real word problem!
Quote from: ludermor on February 18, 2015, 02:50:52 PM
Probably more algerbra than excel question!
im trying to find out actual wages from one of my subcontractor and he has 3 different pay grades for trades.?
A good estimate would be 8470 for Tom, 4600 for John and 5380 for Mick.
Sorry, I meant to explain where I got that estimate from (there are plenty of feasible solutions)
If you subtract one equation from the other, you end up with
1527 = 0.17T + 0.02J + 0.04M
So that 1527 is made up of 17% Tom plus 2% and 4% which I assume are negligible (say €100) - this estimate determines what your final result will be.
From that, you can calculate all three.
The assumption in that however is that the total bill consists of the aggregate of the wages and nothing else.
Quote from: gallsman on February 18, 2015, 08:58:30 PM
The assumption in that however is that the total bill consists of the aggregate of the wages and nothing else.
the assumption would be correct
Quote from: macdanger2 on February 18, 2015, 08:49:07 PM
Sorry, I meant to explain where I got that estimate from (there are plenty of feasible solutions)
If you subtract one equation from the other, you end up with
1527 = 0.17T + 0.02J + 0.04M
So that 1527 is made up of 17% Tom plus 2% and 4% which I assume are negligible (say €100) - this estimate determines what your final result will be.
From that, you can calculate all three.
Cheers Mac, I'll play around and see how they work with a couple of other months
Quote from: macdanger2 on February 18, 2015, 08:49:07 PM
Sorry, I meant to explain where I got that estimate from (there are plenty of feasible solutions)
If you subtract one equation from the other, you end up with
1527 = 0.17T + 0.02J + 0.04M
So that 1527 is made up of 17% Tom plus 2% and 4% which I assume are negligible (say €100) - this estimate determines what your final result will be.
From that, you can calculate all three.
Should it not be €1,527 = 0.17T
- 0.02J + 0.04M
The difference will be very small but it is interesting that John's went down even though the overall cost of the job went up. Thus (unless John is being shafted) the relationships are not linear and that makes it harder to work out.
Suggest you try to find out how much extra Mick made in the second month, or John lost, over a coffee or other casual chat. And us the above to work it out.
Sorry, yeah should be minus the 0.02 alright but same idea applies
Quote from: muppet on February 18, 2015, 11:07:40 PM
Quote from: macdanger2 on February 18, 2015, 08:49:07 PM
Sorry, I meant to explain where I got that estimate from (there are plenty of feasible solutions)
If you subtract one equation from the other, you end up with
1527 = 0.17T + 0.02J + 0.04M
So that 1527 is made up of 17% Tom plus 2% and 4% which I assume are negligible (say €100) - this estimate determines what your final result will be.
From that, you can calculate all three.
Should it not be €1,527 = 0.17T - 0.02J + 0.04M
The difference will be very small but it is interesting that John's went down even though the overall cost of the job went up. Thus (unless John is being shafted) the relationships are not linear and that makes it harder to work out.
Suggest you try to find out how much extra Mick made in the second month, or John lost, over a coffee or other casual chat. And us the above to work it out.
They would not be linear, Tom would earn a lot more than John who should be earning more than Mick. I shoudl know more in the next 2 weeks when i get the monthly application.
I have quick question for the excel experts. I have some information I want sort quickly.
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33 aox 47
4 aox 47 rst 35
Products are in A and C amounts in B & D
I need it to kick out a gap where the product isn't in both A&C
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33
4 aox 47 aox 47
5 rst 35
Anyone?
Highlight cells C3 and D3, click insert cells, select move cells down.
Think that should do what you're after
Quote from: laceer on February 19, 2015, 01:07:31 PM
Highlight cells C3 and D3, click insert cells, select move cells down.
Think that should do what you're after
Thanks but that is the manual version. I have a huge spreadsheet that this jobs needs to be done to time and time again.That process would take hours.
How frequently in the spreadsheet do you have to bump these couple of cells down? Is it recurring?
Quote from: gerrykeegan on February 19, 2015, 12:41:07 PM
I have quick question for the excel experts. I have some information I want sort quickly.
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33 aox 47
4 aox 47 rst 35
Products are in A and C amounts in B & D
I need it to kick out a gap where the product isn't in both A&C
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33
4 aox 47 aox 47
5 rst 35
Anyone?
Where do you need the gap, in row 3 you have it in column C, in row 5 you have it in column A
Quote from: Mario on February 20, 2015, 08:35:25 AM
Quote from: gerrykeegan on February 19, 2015, 12:41:07 PM
I have quick question for the excel experts. I have some information I want sort quickly.
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33 aox 47
4 aox 47 rst 35
Products are in A and C amounts in B & D
I need it to kick out a gap where the product isn't in both A&C
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33
4 aox 47 aox 47
5 rst 35
Anyone?
Where do you need the gap, in row 3 you have it in column C, in row 5 you have it in column A
I need the gap where ever the product is not in both A&C. My first table is how my data looks now. My second table is the result of a sort I need to do. Product avd is not in C&D thats why I need a gap to appear. Product aox was not lined up in the first table is is in the second.
You might be able to do something on a new sheet using the LOOKUP function, if the products were in alphabetical order on the first column/page.
Gerry - see your "rst" in row 4.
Does this just appear once, in that position and do subsequent rows continue in normal order, with two sets of matching values?
As in:
Row 4: aox 47 rst 35
Row 5: xyz 75 xyz 75
Row 6: abc 90 abc 90
OR
Does the "rst" appear in the first column of row 5, with all the remaining values offset from there?
As in:
Row 4: aox 47 rst 35
Row 5: rst 35 xyz75
Row 6: xyz 75 abc 90
Quote from: gerrykeegan on February 20, 2015, 09:30:10 AM
Quote from: Mario on February 20, 2015, 08:35:25 AM
Quote from: gerrykeegan on February 19, 2015, 12:41:07 PM
I have quick question for the excel experts. I have some information I want sort quickly.
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33 aox 47
4 aox 47 rst 35
Products are in A and C amounts in B & D
I need it to kick out a gap where the product isn't in both A&C
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33
4 aox 47 aox 47
5 rst 35
Anyone?
Where do you need the gap, in row 3 you have it in column C, in row 5 you have it in column A
I need the gap where ever the product is not in both A&C. My first table is how my data looks now. My second table is the result of a sort I need to do. Product avd is not in C&D thats why I need a gap to appear. Product aox was not lined up in the first table is is in the second.
Will the total in B always equal the total in D?
Try this:
https://www.dropbox.com/s/zfzpb8ogty5lf9k/Solution1.xlsx?dl=0 (https://www.dropbox.com/s/zfzpb8ogty5lf9k/Solution1.xlsx?dl=0)
Quote from: ludermor on February 20, 2015, 11:16:51 AM
Quote from: gerrykeegan on February 20, 2015, 09:30:10 AM
Quote from: Mario on February 20, 2015, 08:35:25 AM
Quote from: gerrykeegan on February 19, 2015, 12:41:07 PM
I have quick question for the excel experts. I have some information I want sort quickly.
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33 aox 47
4 aox 47 rst 35
Products are in A and C amounts in B & D
I need it to kick out a gap where the product isn't in both A&C
A B C D
1 alg 55 alg 55
2 als 45 als 45
3 avd 33
4 aox 47 aox 47
5 rst 35
Anyone?
Where do you need the gap, in row 3 you have it in column C, in row 5 you have it in column A
I need the gap where ever the product is not in both A&C. My first table is how my data looks now. My second table is the result of a sort I need to do. Product avd is not in C&D thats why I need a gap to appear. Product aox was not lined up in the first table is is in the second.
Will the total in B always equal the total in D?
No in fact B & D will always be different, for simplicity I left them the same. I am combining two sets of data. A&C are the product code. I am trying to align each product code. B&D are data (say sales and quantities) of the product code. However not all products appear in A & C.
Product Stock Product Sale
Apple 50 Apple 125
Melon 43 Banana 17
Orange 62 Melon 143
Peach 112 Pear 175
Pear 53
1. Sales of banana but no stock
2. Stock of orange but no sales
3. Stock of peach but no sales
When sort is completed
Product Stock Product Sale
Apple 50 Apple 125
Banana 17
Melon 43 Melon 143
Orange 62
Peach 112
Pear 53 Pear 175
Basically I want to line up the data without having to manually
push down the data to line up.
A pivot table might do it. Select all data, insert pivot table, drag Product in the row label, sum of sales and sum of stock into values. Should give you a list of all products, the sum of their stock and sum if their sales. Where there are no stock or sales there will be a blank in your pivot table.
I presume you have a list of all of the possible items?
Say the raw data in the table below in in Excel in cells A1 to D6. Setup four new columns to the right (columns E to H) with the same headings (Product, Stock, Product, Sale).
In the two Product columns, paste in the full list of all possible items.
In column F, cell F2 input the following formula: "=VLOOKUP(E2,A:B,2,FALSE)"
In column H, cell H2 input the following formula: "=VLOOKUP(G2,C:D,2,FALSE)"
Drag that down to the end of your datasheet and it should do what you want.
Just to explain what you're doing with the VLOOKUP is:
1) Lookup the value in E2 (say "apple") in the matrix A:B (i.e. all rows in columns A-B)
2) if you find the value "apple" in column A then return the value in the second column of the matrix (i.e. column B).
3) The "FALSE" just means you're looking for an exact match. If no match is found, "N/A" is returned
That just gave me my raw data exactly as it was. Am I missing something. I got no gaps
Did you paste the FULL list of possible products in column E & G? That's where the gaps come from i.e. when something isn't found, n/a is returned
Quote from: macdanger2 on February 20, 2015, 05:17:40 PM
Did you paste the FULL list of possible products in column E & G? That's where the gaps come from i.e. when something isn't found, n/a is returned
I did I copy all the data into the adjacent cells. The formula worked but it didn't create the gaps. In the end I just manually inserted the gaps as I needed to get the job done. Thanks for your help and I now know something about Lookup!
No bother.
In the example below, you would have pasted apple, melon, orange, peach, pear, banana into both columns. Don't know why it wouldn't have worked
Hi
Hoping someone can help me with abit of vba, I need to find and replace alot of data, if i just use the standard across the entire sheet, its to big and excel just crashes. It's usually just done using the find and replace on a column by column basis! each column takes 15/20 mins before i can start the next. So i was thinking a macro could be written to just set this off once that can be left until they are all done. I was thinking of using the following:
Sub Replace"()
Columns("A:BZ").Replace What:=""", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub
I was wondering if anyone knows whether this will run on a column by column basis or is it likely to crash just like the standard find and replace?
Sorry, don't know much about VBA.
I wouldn't have thought excel should crash on a find & replace though - how many rows of data are in the columns?
About 350k! I've set it off to run, it didn't like the triple quotation, so i added another and it seems to be going ok!
Quote from: macdanger2 on February 20, 2015, 02:58:43 PM
I presume you have a list of all of the possible items?
Say the raw data in the table below in in Excel in cells A1 to D6. Setup four new columns to the right (columns E to H) with the same headings (Product, Stock, Product, Sale).
In the two Product columns, paste in the full list of all possible items.
In column F, cell F2 input the following formula: "=VLOOKUP(E2,A:B,2,FALSE)"
In column H, cell H2 input the following formula: "=VLOOKUP(G2,C:D,2,FALSE)"
Drag that down to the end of your datasheet and it should do what you want.
Just to explain what you're doing with the VLOOKUP is:
1) Lookup the value in E2 (say "apple") in the matrix A:B (i.e. all rows in columns A-B)
2) if you find the value "apple" in column A then return the value in the second column of the matrix (i.e. column B).
3) The "FALSE" just means you're looking for an exact match. If no match is found, "N/A" is returned
Is there any way to get rid of the N/A in LOOKUP?
I'm running two lookup columns in a control spreadsheet which are the two options of where my answer is. I'd like to run this in one column sort of an =if formula incorporating two lookup equations.
As my answer will always be a number and there is no overlap in the two locations a simple sum(vlookup+vlookup) would work if my negative answer was 0 instead of #N/A.
Any ideas?
=IF(ISNA(VLOOKUP(A4,$A$9:$C$11,3,FALSE)),VLOOKUP(A4,$B$9:$C$11,2,FALSE),VLOOKUP(A4,$A$9:$C$11,3,FALSE))
That should work if i understand what you are looking to do! the logic test will check if the vlookup will return an error (value not in the list), if that is true, then it will give the value using the second vlookup using the second column, and if there is no error it will use the original column!
Probably haven't explained that in the best way...
Worked eventually, once i got over my inherent stupidity, thanks.
Edit to add i'd have got it first go with your edited explanation, although a bit of trial and error is no harm.
I have an excel spreadsheet which contains formula (nothing to complex) and is used quite a bit. I would like to be able use this on a smartphone/blackberry.
Is there way I can do this? say convert to a simple app or such?
Quote from: pullhard on April 30, 2015, 01:47:51 PM
I have an excel spreadsheet which contains formula (nothing to complex) and is used quite a bit. I would like to be able use this on a smartphone/blackberry.
Is there way I can do this? say convert to a simple app or such?
Does "smartphone/BlackBerry" mean a phone other then a BlackBerry? On android QuickOffice or Google Sheets handles the format fine, no conversion needed.
aye likely android and blackberry.
Would prefer it was a standalone kind app. Is it possible ?
But if will defo look into quick and google
Quote from: pullhard on April 30, 2015, 02:58:18 PM
aye likely android and blackberry.
Would prefer it was a standalone kind app. Is it possible ?
But if will defo look into quick and google
I'm not sure I get you when you say standalone app. You can open the file (stored natively, on the cloud, from email etc) and use either of them to view or edit the spreadsheet and then save it again, no tampering with format required at all.
No ideas about blackberry though, sorry.
Incorrect words used.
Could the app's listed quick and google be used offline?
Quote from: pullhard on April 30, 2015, 03:26:34 PM
Incorrect words used.
Could the app's listed quick and google be used offline?
Yes.
Great thread, this.
I understand neither the questions or the answers.
Bit like the black card thread...
New query for our resident experts on here.
I have a fairly large table with (sometimes) multiple events (date & time) logged against a reference. Is there any way to identify these by event order (they are already sorted in reference and event order.
I have this:
REFERENCE EVENT
1001 12-01-2015
1001 19-01-2015
1001 26-01-2015
1001 02-02-2015
1002 20-03-2015
1002 27-03-2015
1002 03-04-2015
1003 09-03-2015
1004 03-02-2015
1004 17-02-2015
I want to add an additional column:
REFERENCE EVENT ATTEMPT
1001 12-01-2015 1
1001 19-01-2015 2
1001 26-01-2015 3
1001 02-02-2015 4
1002 20-03-2015 1
1002 27-03-2015 2
1002 03-04-2015 3
1003 09-03-2015 1
1004 03-02-2015 1
1004 17-02-2015 2
Each reference can have up to 7 attempts but many may have only 1 or 2. I don't want a COUNT of the number of attempts. For each reference the earliest event should be identified as 1 (or A), the next as 2 (or B), etc.
Put in column c2
=IF(A2=A1,C1+1,1)
Then copy that formula down to the bottom of c column
Quote from: Maiden1 on June 29, 2015, 11:14:16 AM
Put in column c2
=IF(A2=A1,C1+1,1)
Then copy that formula down to the bottom of c column
Perfect. Cheers.
One of the most annoying things about Excel is it's inability to properly auto-height rows.
I had one particular spreadsheet sent to me, and no matter what I tried I couldn't see all of the text in some cells, because the row height wasn't big enough.
In the end, I had to change the font to arial, and then auto row height started to work.
I have a link from a pivot table in one workbook to a separate workbook, each day the pivot table updates and adds an extra row - does anyone know how I can link my excel workbook to the value as opposed to the cell, so that when the pivot table updates and I want the link to draw the value from A2 as opposed to A1 as was the previous days?
Quote from: lfdown2 on September 23, 2015, 09:04:24 AM
I have a link from a pivot table in one workbook to a separate workbook, each day the pivot table updates and adds an extra row - does anyone know how I can link my excel workbook to the value as opposed to the cell, so that when the pivot table updates and I want the link to draw the value from A2 as opposed to A1 as was the previous days?
Not sure it's possible to have the link floating like that.
Is there something about the new line that you could use to pull it into a cell on the same sheet and then just link to that new cel?? For example if the new line has today's date in the left column, you could use that. Or if it's the largest / smallest value, you could use that.
Quote from: lfdown2 on September 23, 2015, 09:04:24 AM
I have a link from a pivot table in one workbook to a separate workbook, each day the pivot table updates and adds an extra row - does anyone know how I can link my excel workbook to the value as opposed to the cell, so that when the pivot table updates and I want the link to draw the value from A2 as opposed to A1 as was the previous days?
When you are setting up the initial pivot table instead of putting the range as Sheet1!$B$1:$C$4 you could put the range as Sheet1!$B$1:$C$1000. That way you would have the complete range for 1000 days. After you set it up you can filter out blanks.
Quote from: lfdown2 on September 23, 2015, 09:04:24 AM
I have a link from a pivot table in one workbook to a separate workbook, each day the pivot table updates and adds an extra row - does anyone know how I can link my excel workbook to the value as opposed to the cell, so that when the pivot table updates and I want the link to draw the value from A2 as opposed to A1 as was the previous days?
Use a vlookup to look up the value of the item in the pivot table. Then no matter where in the pivot table the item appears you will always get its correct associated value (ie the number you want to return in your other workbook)
cheers lads, ill let ye's know how that goes
OK Excel Gurus, this is probably really simple to ye but I'd appreciate the help.
I have a list of 700 values in a column (say column A) like D01, D02, D06 etc.
In Column B, I have a list of 1700 values in a column, like D01, D02, D03, D05 etc,
So essentially the 1700 rows in column b are a super set of column A.
What I am trying to do, via Lookup is to identify, in a column C, which of the Column B values are in the subset in Column A.
What I am looking for is basically like this
D01 D01 Yes
D04 D02 No
D06 D03 No
D04 Yes
D05 No
D06 Yes
I almost had it with lookup, but the best I could have was to put the D number itself into Column C if it was found, and I really want to have something a bit neater than that.
Cheers
Quote from: AZOffaly on October 15, 2015, 11:44:26 AM
OK Excel Gurus, this is probably really simple to ye but I'd appreciate the help.
I have a list of 700 values in a column (say column A) like D01, D02, D06 etc.
In Column B, I have a list of 1700 values in a column, like D01, D02, D03, D05 etc,
So essentially the 1700 rows in column b are a super set of column A.
What I am trying to do, via Lookup is to identify, in a column C, which of the Column B values are in the subset in Column A.
What I am looking for is basically like this
D01 D01 Yes
D04 D02 No
D06 D03 No
D04 Yes
D05 No
D06 Yes
I almost had it with lookup, but the best I could have was to put the D number itself into Column C if it was found, and I really want to have something a bit neater than that.
Cheers
Add an IF function.
=If (vlookup(......),"Yes", "No")
If(vlookup(B1,A1:A700,1),"Yes","No") ??
=IF(ISNA(VLOOKUP(B4,$A$1:$A$3,1,FALSE)),"No","Yes")
Brilliant stuff. Works like a charm. Thanks a million.
Dug out this topic to look for help on a similar issue to AZ (coincidentally). Large spreadsheet interrogating a smaller one where I wanted to leave blanks rather than #N/A in every non matching cell. Starting from Majestic's post i got
=IF(ISNA(VLOOKUP(A12,[070115.xlsx]AOPR!$B$11:$AD$17,18,FALSE)),"",VLOOKUP(A12,[070115.xlsx]AOPR!$B$11:$AD$17,18,FALSE))
which gives me the entry I want or a blank.
Cheers Majestic
Ok folks looking for some help here. I'm recording some data in years and months, eg Cell A1 7 years and 2 months, A2 8 years and 9 months. In A3 I want to use a formula to calculate the difference. What's my best way of doing this? Some idea of how to use excel, but I'm no expert.
Quote from: townof12 on April 01, 2016, 10:20:41 PM
Ok folks looking for some help here. I'm recording some data in years and months, eg Cell A1 7 years and 2 months, A2 8 years and 9 months. In A3 I want to use a formula to calculate the difference. What's my best way of doing this? Some idea of how to use excel, but I'm no expert.
Is it literally written as "7 years and 2 months"?
Quote from: armaghniac on April 01, 2016, 10:24:45 PM
Quote from: townof12 on April 01, 2016, 10:20:41 PM
Ok folks looking for some help here. I'm recording some data in years and months, eg Cell A1 7 years and 2 months, A2 8 years and 9 months. In A3 I want to use a formula to calculate the difference. What's my best way of doing this? Some idea of how to use excel, but I'm no expert.
Is it literally written as "7 years and 2 months"?
I formatted the cell with 'custom' and then 00 "Year(s)," 00 "Month(s)"
This displays the data fine for me, but when I try to calculate the difference between the 2 cells, it isn't accurate.
check out =DATEDIF
you need something like =DATEDIF(A1,A2,"YM");
Quote from: armaghniac on April 01, 2016, 10:35:38 PM
check out =DATEDIF
you need something like =DATEDIF(A1,A2,"YM");
Nah, not working for me, giving up for the night.
Thanks for the help though.
Is it just number of days difference.
OK, Excel heads, #I have a new one I am struggling with.
I have an Excel spreadsheet with a series of centres with events and dates. I would like to be able to see the most recent activity for that centre by pulling out the most recent date per centre.
Centre Event Date New column
1 a 01/01/2016 01/04/2016
1 b 01/02/2016 01/04/2016
2 a 01/02/2016 01/02/2016
3 a 01/03/2016 01/04/2016
4 a 01/03/2016 01/03/2016
1 c 01/04/2016 01/04/2016
3 b 01/04/2016 01/04/2016
To my mind I need an IF statement IF(a1:a9,=a1) to identify the centres then some sort of evaluation to pull the highest value from column C list but I dont know how to go about putting it together.
try creating a new table with your distinct centre numbers starting at A11
then use the below for each centre
=MAX(IF($A$1:$A$9=A11,$C$1:$C$9)) where C1 to C9 are your dates
that will return the most recent date for each centre but not the event.
Centre Date
1 =MAX(IF($A$1:$A$9=A11,$C$1:$C$9))
2 =MAX(IF($A$1:$A$9=A12,$C$1:$C$9))
3 =MAX(IF($A$1:$A$9=A13,$C$1:$C$9))
4 =MAX(IF($A$1:$A$9=A14,$C$1:$C$9))
you need to use ctrl + shift + enter when formula is complete or you will see an error
Quote from: LeoMc on May 11, 2016, 11:08:24 AM
OK, Excel heads, #I have a new one I am struggling with.
I have an Excel spreadsheet with a series of centres with events and dates. I would like to be able to see the most recent activity for that centre by pulling out the most recent date per centre.
Centre Event Date New column
1 a 01/01/2016 01/04/2016
1 b 01/02/2016 01/04/2016
2 a 01/02/2016 01/02/2016
3 a 01/03/2016 01/04/2016
4 a 01/03/2016 01/03/2016
1 c 01/04/2016 01/04/2016
3 b 01/04/2016 01/04/2016
To my mind I need an IF statement IF(a1:a9,=a1) to identify the centres then some sort of evaluation to pull the highest value from column C list but I dont know how to go about putting it together.
If you want it in the format you've discussed then these guys seem to be proposing different ways of doing it
http://stackoverflow.com/questions/10755207/how-to-get-max-date-from-one-column-and-match-with-other-column-in-excel
If all you want to know is the last date and centre for each event then some version of advanced filter or sorting would seem the way forward.
Thanks both. Tehre are approx 600 centres with up to 48 events across each over a 4 year period so I had been hoping to keep any formulae in the exisitng table rather than creating new rows. I will keep tinkering.
Can't remember how to do this!
I have 3 temperature readings on a graph which are supposed to be +/- 5degC from 3 test points (10, 50 and 90degC). The 90degC test point has an as found value of 95.76degC and I want to do a regression to see at exactly what temperature between 50 amd 90 it crossed the 5degC threshold.
Cheers.
Quote from: Tony Baloney on January 31, 2018, 09:42:20 PM
Can't remember how to do this!
I have 3 temperature readings on a graph which are supposed to be +/- 5degC from 3 test points (10, 50 and 90degC). The 90degC test point has an as found value of 95.76degC and I want to do a regression to see at exactly what temperature between 50 amd 90 it crossed the 5degC threshold.
What is the other data? Time?
Quote from: armaghniac on January 31, 2018, 09:54:38 PM
Quote from: Tony Baloney on January 31, 2018, 09:42:20 PM
Can't remember how to do this!
I have 3 temperature readings on a graph which are supposed to be +/- 5degC from 3 test points (10, 50 and 90degC). The 90degC test point has an as found value of 95.76degC and I want to do a regression to see at exactly what temperature between 50 amd 90 it crossed the 5degC threshold.
What is the other data? Time?
2 columns
Setpoint Recorded Value
10 10.58
50 52.34
90 95.76
I want to work backwards to see at what point between 50 and 90 the 5.0 limit was breached. I used to be able to do this but haven't used that part of my brain for years :(
Quote from: Tony Baloney on January 31, 2018, 10:04:20 PM
Quote from: armaghniac on January 31, 2018, 09:54:38 PM
Quote from: Tony Baloney on January 31, 2018, 09:42:20 PM
Can't remember how to do this!
I have 3 temperature readings on a graph which are supposed to be +/- 5degC from 3 test points (10, 50 and 90degC). The 90degC test point has an as found value of 95.76degC and I want to do a regression to see at exactly what temperature between 50 amd 90 it crossed the 5degC threshold.
What is the other data? Time?
2 columns
Setpoint Recorded Value
10 10.58
50 52.34
90 95.76
I want to work backwards to see at what point between 50 and 90 the 5.0 limit was breached. I used to be able to do this but haven't used that part of my brain for years :(
Regression is in Data -> Data Analysis, but this may not be enabled in your Excel, so you need to go to File Options to make this visible.
I would make the answer around 84.87.
My neighbour asked me to help with a problem. He has a simple workbook that includes sums at the bottom of columns. When he enters a sum in one or two particular columns and then tries to save the spreadsheet, it fails to save and Excel closes. Windows 10, Excel 2010 (I think). The problem doesn't occur when I do the same with the same workbook on my own laptop with W7, Excel 2010.
HAs anyone seen anything like this? I've been through the forums and found nothing that works. (Automatic recalculation is set in Excel options.)
Is he saving it as an excel doc?
It's an issue we have with reports in work, they are exported as csv files and unless you save as, and set file type to excel doc the changes are lost.
Yes - saving as .xlsx file.
Quote from: Hardy on June 21, 2018, 10:56:17 AM
Yes - saving as .xlsx file.
Have you tried saving it as .xls (old format)? Maybe save it in this format before trying the problematic calculations.
Sorry, checked again - it is already .xls not .xlsx.
I have two sheets, A and B.
A contains 20 columns of data and B contains 12, most of which will be a copy of corresponding data from A.
One of the columns in Sheet A is a "Y/N" flag that indicates whether data in the corresponding row should copy across to Sheet B.
Say the first row of data in A is Row 3 and is flagged Y. I want this data to copy across to Row 3 in B.
Then the next row in A with the Y flag is Row 10. I want this data to copy across to Row 4 in B.
Any ideas?
Quote from: gallsman on January 07, 2019, 11:20:04 AM
I have two sheets, A and B.
A contains 20 columns of data and B contains 12, most of which will be a copy of corresponding data from A.
One of the columns in Sheet A is a "Y/N" flag that indicates whether data in the corresponding row should copy across to Sheet B.
Say the first row of data in A is Row 3 and is flagged Y. I want this data to copy across to Row 3 in B.
Then the next row in A with the Y flag is Row 10. I want this data to copy across to Row 4 in B.
Any ideas?
Do you want to copy this as a once off operation or have a continually updating sheet B?
A once off is just a question of filtering the column with Y/N and copying.
Quote from: armaghniac on January 07, 2019, 12:21:05 PM
Quote from: gallsman on January 07, 2019, 11:20:04 AM
I have two sheets, A and B.
A contains 20 columns of data and B contains 12, most of which will be a copy of corresponding data from A.
One of the columns in Sheet A is a "Y/N" flag that indicates whether data in the corresponding row should copy across to Sheet B.
Say the first row of data in A is Row 3 and is flagged Y. I want this data to copy across to Row 3 in B.
Then the next row in A with the Y flag is Row 10. I want this data to copy across to Row 4 in B.
Any ideas?
Do you want to copy this as a once off operation or have a continually updating sheet B?
A once off is just a question of filtering the column with Y/N and copying.
Sorry, should have been clear. Sheet A is a live document and will be continuously updated.
You could use 2 new columns in sheet A
Column 1;
=VLOOKUP(A2,SheetB!A:D,3,0)
This looks in sheet B for the corresponding data - where A2 =A2, then return column 3
Column 2:
=IF(C2="Yes",D2,"")
This displays the data returned in the lookup if C2 = Yes otherwise leaves in blank
EDIT: - no need for 2 columns, just put the vlookup inside the if
=IF(C2="Yes",VLOOKUP(A2,SheetB!A:D,3,0),"")
Quote from: WeeDonns on January 07, 2019, 12:58:59 PM
You could use 2 new columns in sheet A
Column 1;
=VLOOKUP(A2,SheetB!A:D,3,0)
This looks in sheet B for the corresponding data - where A2 =A2, then return column 3
Column 2:
=IF(C2="Yes",D2,"")
This displays the data returned in the lookup if C2 = Yes otherwise leaves in blank
EDIT: - no need for 2 columns, just put the vlookup inside the if
=IF(C2="Yes",VLOOKUP(A2,SheetB!A:D,3,0),"")
Sheet A has the master data and the indicator/flag. Sheet B is the destination and is to only hold copied data from A.
The above would still leave blank rows, no?
What I want is that each successive row that has the Y flag in A copies to the next available row in B. Google sheets has a FILTER() formula for this but Excel doesn't.
Yeah it would leave blank rows
put the formulae in SheetB & add a filter to the column headings to not display blank values
fill the formula down all rows
Quote from: WeeDonns on January 07, 2019, 01:41:22 PM
Yeah it would leave blank rows
put the formulae in SheetB & add a filter to the column headings to not display blank values
fill the formula down all rows
I need it to be tidier than that unfortunately.
Google Sheets has a FILTER function that does literally the exact thing I'm looking for but Excel only have a beta of it at the minute.
Quote from: gallsman on January 07, 2019, 02:20:51 PM
Quote from: WeeDonns on January 07, 2019, 01:41:22 PM
Yeah it would leave blank rows
put the formulae in SheetB & add a filter to the column headings to not display blank values
fill the formula down all rows
I need it to be tidier than that unfortunately.
Google Sheets has a FILTER function that does literally the exact thing I'm looking for but Excel only have a beta of it at the minute.
I think below does what you are looking for but it was making my head hurt trying to follow it.
https://www.youtube.com/watch?v=tqCEY5YMyqw
Quote from: Maiden1 on January 07, 2019, 03:07:58 PM
Quote from: gallsman on January 07, 2019, 02:20:51 PM
Quote from: WeeDonns on January 07, 2019, 01:41:22 PM
Yeah it would leave blank rows
put the formulae in SheetB & add a filter to the column headings to not display blank values
fill the formula down all rows
I need it to be tidier than that unfortunately.
Google Sheets has a FILTER function that does literally the exact thing I'm looking for but Excel only have a beta of it at the minute.
I think below does what you are looking for but it was making my head hurt trying to follow it.
https://www.youtube.com/watch?v=tqCEY5YMyqw
This is an easier to follow article, you can put all matches on a separate tab
https://www.eimagine.com/how-to-return-multiple-match-values-in-excel-using-index-match-or-vlookup/
Quote from: gallsman on January 07, 2019, 11:20:04 AM
I have two sheets, A and B.
A contains 20 columns of data and B contains 12, most of which will be a copy of corresponding data from A.
One of the columns in Sheet A is a "Y/N" flag that indicates whether data in the corresponding row should copy across to Sheet B.
Say the first row of data in A is Row 3 and is flagged Y. I want this data to copy across to Row 3 in B.
Then the next row in A with the Y flag is Row 10. I want this data to copy across to Row 4 in B.
Any ideas?
Use this formula pasted into cell A2 of sheet 2:
=IFERROR(INDEX(Sheet1!$B$2:$B$1000,SMALL(IF(Sheet1!$C$2:$C$1000=$A$1,ROW(Sheet1!$B$2:$B$1000)-MIN(ROW(Sheet1!$B$2:$B$1000))+1),ROWS($A$2:A2))),"")
And after you paste it into the cell, you need to hit SHIFT+CTRL+Enter - this will create an array and you'll see these brackets {} around the formula in the formula bar (without you having typed them). Any time you edit the formula, you'll need to close the cell using SHIFT+CTRL+Enter or the formula won't work.
In this example, Y (or whatever you're searching for) is placed in cell A1, change this location to wherever you want.
The marker (i.e. Y or N) in the original table is located in Sheet1!$C$2:$C$1000
The value in the original table to be returned is located in Sheet1!$B$2:$B$1000
You can drag it down and it should pull only rows with Ys in the marker columns
Hope that works for you, let me know if it doesn't
(https://uploads.disquscdn.com/images/446ae128d33010b54967ade3bfd42880b4f7d05a9ceb320b548e077a60f771bf.jpg)
Will try that macdanger, thanks.
Problem:
In cell F9 I have a date that I would like to change dependent on the value in another cell. That cell will be in the range I9:JR9 and will contain the number 1. In row 6 there are dates in sequential order in cells I6:JR6. So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9. If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.
Near solution:
=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))
This works if I manually type in the number but not when I drag it left to right.
Any ideas?
Quote from: andoireabu on April 23, 2020, 12:14:42 PM
Problem:
In cell F9 I have a date that I would like to change dependent on the value in another cell. That cell will be in the range I9:JR9 and will contain the number 1. In row 6 there are dates in sequential order in cells I6:JR6. So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9. If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.
Near solution:
=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))
This works if I manually type in the number but not when I drag it left to right.
Any ideas?
Try removing the $ symbol
Quote from: andoireabu on April 23, 2020, 12:14:42 PM
Problem:
In cell F9 I have a date that I would like to change dependent on the value in another cell. That cell will be in the range I9:JR9 and will contain the number 1. In row 6 there are dates in sequential order in cells I6:JR6. So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9. If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.
Near solution:
=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))
This works if I manually type in the number but not when I drag it left to right.
Any ideas?
If I understand what you are trying to do right, your formula worked for me... By drag it left to right I assume you mean there is still only one "1" entry in row 9 rather than copying?
Did you try fully fixing the ranges?
=INDEX($I$6:$JR$6, MATCH(1,$J$9:$JR$9,0))
Quote from: RadioGAAGAA on April 23, 2020, 01:49:13 PM
Quote from: andoireabu on April 23, 2020, 12:14:42 PM
Problem:
In cell F9 I have a date that I would like to change dependent on the value in another cell. That cell will be in the range I9:JR9 and will contain the number 1. In row 6 there are dates in sequential order in cells I6:JR6. So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9. If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.
Near solution:
=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))
This works if I manually type in the number but not when I drag it left to right.
Any ideas?
If I understand what you are trying to do right, your formula worked for me... By drag it left to right I assume you mean there is still only one "1" entry in row 9 rather than copying?
Did you try fully fixing the ranges?
=INDEX($I$6:$JR$6, MATCH(1,$J$9:$JR$9,0))
Aye there will only ever be one number 1 in the row. Even when I add the extra $ signs, as soon as I drag the cell to one side the formula changes in F9.
Think I have a work around with HLOOKUP instead that seems to be doing what I want it to.
Quote from: andoireabu on April 23, 2020, 03:02:17 PM
Quote from: RadioGAAGAA on April 23, 2020, 01:49:13 PM
Quote from: andoireabu on April 23, 2020, 12:14:42 PM
Problem:
In cell F9 I have a date that I would like to change dependent on the value in another cell. That cell will be in the range I9:JR9 and will contain the number 1. In row 6 there are dates in sequential order in cells I6:JR6. So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9. If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.
Near solution:
=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))
This works if I manually type in the number but not when I drag it left to right.
Any ideas?
If I understand what you are trying to do right, your formula worked for me... By drag it left to right I assume you mean there is still only one "1" entry in row 9 rather than copying?
Did you try fully fixing the ranges?
=INDEX($I$6:$JR$6, MATCH(1,$J$9:$JR$9,0))
Aye there will only ever be one number 1 in the row. Even when I add the extra $ signs, as soon as I drag the cell to one side the formula changes in F9.
Think I have a work around with HLOOKUP instead that seems to be doing what I want it to.
=LOOKUP(1,$I$9:$JR$10,$I$6:$JR$6)
Should work
HLOOKUP will work if you put the 1 above the date as it does the lookup for the match on the highest row
=HLOOKUP(1,$I$5:$JR$6,2,FALSE)
any excel experts on here should be on the phone to Dido Harding and Serco offering their services to their world class test and trace system which they've based on an excel database.........
"Public Health England's interim chief executive Michael Brodie said a "technical issue" was identified overnight on Friday, 2 October in the process that transfers Covid-19 positive lab results into reporting dashboards. He said the majority of the unreported cases had occurred in the "most recent days".
It was caused by some data files reporting positive test results exceeding the maximum file size.
Mr Brodie said they worked with NHS Test and Trace to "quickly resolve the issue and transferred all outstanding cases immediately into the NHS Test and Trace contact tracing system".
"We fully understand the concern this may cause and further robust measures have been put in place as a result," he said."
https://www.bbc.co.uk/news/uk-54412581 (https://www.bbc.co.uk/news/uk-54412581)
that would be funny except how much would have been spent on that? this was/is not a difficult piece of software to engineer and that is a schoolboy mistake for a "live" application.
Probably a basic enough question for anyone who knows what they are doing but I want to compare cell colours and highlight mismatches.
I've combined the output from 2 risk assessments onto one sheet however
I dont want to change the cell values as Column C was performed using one methodology and Column D by another. Where they haven't reached the same conclusion I want to highlight those rows and then discuss with the people involved.
Column B is the item under assessment, Column C is Risk Assessment Type 1 which is conditional formatted where H = Red, M = Amber and L = Green. Column D is Risk Assessment Type 2 but based on numbers i.e.>8 = Red, 5-7 = Amber and 2-4 = Green.
Not 100% if this is what you need Baloney but how about forgetting the colours and creating a further column based on the values in B and C. For example, High in Column B and 10 in C could be an A value, High in Column B and 9 in C could be a B value and so on and so forth until eventually you'll have all your values however anything that doesn't fit in ie High and 1 wouldn't have a value applied and you could sort it based on this. Don't know if that makes sense!!!!
Quote from: An Watcher on April 16, 2021, 07:12:12 PM
Not 100% if this is what you need Baloney but how about forgetting the colours and creating a further column based on the values in B and C. For example, High in Column B and 10 in C could be an A value, High in Column B and 9 in C could be a B value and so on and so forth until eventually you'll have all your values however anything that doesn't fit in ie High and 1 wouldn't have a value applied and you could sort it based on this. Don't know if that makes sense!!!!
There doesn't seem to be anyway of using a function to identify the colour of cell, so you can either do as Watcher suggests and access the original data or else do a custom function in VB.
Quote from: armaghniac on April 16, 2021, 08:33:25 PM
Quote from: An Watcher on April 16, 2021, 07:12:12 PM
Not 100% if this is what you need Baloney but how about forgetting the colours and creating a further column based on the values in B and C. For example, High in Column B and 10 in C could be an A value, High in Column B and 9 in C could be a B value and so on and so forth until eventually you'll have all your values however anything that doesn't fit in ie High and 1 wouldn't have a value applied and you could sort it based on this. Don't know if that makes sense!!!!
There doesn't seem to be anyway of using a function to identify the colour of cell, so you can either do as Watcher suggests and access the original data or else do a custom function in VB.
Cheers lads I'll take a look at matching them. On Monday obviously!
Quote from: Tony Baloney on April 16, 2021, 05:55:30 PM
Probably a basic enough question for anyone who knows what they are doing but I want to compare cell colours and highlight mismatches.
I've combined the output from 2 risk assessments onto one sheet however
I dont want to change the cell values as Column C was performed using one methodology and Column D by another. Where they haven't reached the same conclusion I want to highlight those rows and then discuss with the people involved.
Column B is the item under assessment, Column C is Risk Assessment Type 1 which is conditional formatted where H = Red, M = Amber and L = Green. Column D is Risk Assessment Type 2 but based on numbers i.e.>8 = Red, 5-7 = Amber and 2-4 = Green.
The problem seems to be that you can't play atound with the colours cokumn. Could you set up another cokumn and apply the same colour scheme to.Column D so the 2 are on the same basis?.