Excel Questions

[townof12]

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.

[Aaron Boone]

Is it just number of days difference.

[LeoMc]

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.

[Link]

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

[armaghniac]

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.

[LeoMc]

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.

[Tony Baloney]

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.

[armaghniac]

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?

[Tony Baloney]

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 

[armaghniac]

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.

[Hardy]

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

[lfdown2]

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.

[Hardy]

Yes - saving as .xlsx file.

[armaghniac]

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.

[Hardy]

Sorry, checked again - it is already .xls not .xlsx.