/m1998

Hello Richard,

I can't see how you can do this the way you decribe.

However, this might work. Create a table that bands the time slots. I have lower limits in I and upper limits in J and I slice time up into 15min slots.

My data is in a table E5:G7.

Then I have three formulae for each time slot. The first counts the number of times a time falls within the limits. The second adds up the times and the third calcs the average.

=SUMPRODUCT(($E$5:$G$7>=$I5)*($E$5:$G$7<$J5))

=SUMPRODUCT(($E$5:$G$7>=$I5)*($E$5:$G$7<$J5)*($E$5:$G$7))

These formulae can be copied about to quickly get the data for all the time slots and the average is easily calc'd.

I hope you can see what this does. It may not give you exactly the results you need but it could be close.

Give it a try and let me know how you get on.

Good luck.

Hi Again

Oh my goodness me! Just worked out the INT()!!!!

Sorry - the note threw me!!!

Heather

Hello Guys

I have been sent a spread sheet to amend and have come across something I am unfamiliar with - could anyone enlighten me please.

The cell formula is - =INT(C15/DoYourClientsPayTheirWay!$F$4/DoYourClientsPayTheirWay!$E$4)

It is the INT part of the formula I am struggling with.

There is a note refering to the cell that says - "CALCULATED FIGURES:
Figures in this column are calculated from the PRE CALC values and the DATA INPUTS you provided."

Am I correct in thinking the INT is the "PRE CALC"?
How was the "PRE CALC" SET?
Can I view it and amend it?


Many thanks
Heather

Edited by author 25-04-2008 10:48 AM
Hello Richard

Thanks for your response. In the columns I have the time data, these times are vehicle depatch times and the times inrease down the column,
A1 = Sat 07:30
A2 = Sat 08:21
A3 = Sat 08:24 and so on.

Column B has similar data but the times differ,
so
B1 may be Sat 07:24
B2 could be Sat 09:45!

I want to match the nearest time across the columns and then produce an average time to the nearest half hour.
Currently I have to move the column data up or down manually to align the times then assign an average,

I could have upwards of 8 or 9 columns or more and the times vary considerably.

The object is to align the nearest time across the columns in order to produce a final time to the nearest half hour by the clock.This will give me the most accurate time to plan staff to take the jobs. The average time produced can not be prior to the actual despatch time so Sat 07:29 must return Sat 07:00 and so on.

Hope this gives you more to go on.

Thanks again.

Richard

/m1996

Hello Richard,

What are you trying to calculate? Is it the number of times in each 30 minute interval? Or the mean time of any data in ABCD that is within the 30 minute interval? Or something else. It might help if you can let us know what the data is and what the result is supposed to do?

Thanks

Hi again all,

I didn't make a very good job of explaining my issue sorry.

I have columns of data containing times: eg Sat 07:30, Sat 08:24 etc ascending through 24 hrs.I want to match column A's times to the nearest time in column B and then C and so on.Then I want to produce an average time to the nearest 30 minutes in the final column.Hopefully this would leave me with a column of times spaced at a minimum of 30 minute intervals.

Can anyone tell me if this is possible please and if so how to acheive it?

Thank you in anticipation.

Richard

/m1992

Hi Kristina,

I can't think of a way of doing this in the way that you want. Could you try this:

put the path and filename in one cell, the text in another. Say A1 and A2. Then A3 might be =hyperlink(A1,A2)

sheet2 cell A3 might have:

=if(sheet1!a1="","",hyperlink(sheet1!a1,sheet1!a2))

Does that make some sense?

/m1991

Hello Alice,

You could use sumif.

=sumif(A2:A8,"=ABC123",B2:B8)

HTH

Thanks Richard Iwill give it a try and let you know!!!

Regards.Richard M
>
< replied-to message removed by QT >

Edited by author 24-04-2008 03:11 PM
Thank you so much for your help.

=IF('Sheet 1'!A1="", "", 'Sheet 1'!A1)

This is the current formula, whereby it will pull through the contents of Sheet 1 Cell A1, If the cell is blank it will return a blank cell instead of a 0.

If there is a hyperlink in cell A1 it is only pulling through the contents; ie if cell A1 has the words Click Here and a hyperlink to C:\Documents and Settings\My Documents\Document1. The cell that has the formula =IF('Sheet 1'!A1="", "", 'Sheet 1'!A1) will only return the Click Here value without the hyperlink.

=Hyperlink(Link_Location, [Friendly Name])

=IF(IF('Sheet 1'!B24='Sheet 1'!B24, HYPERLINK('Sheet 1'!B24, 'Sheet 1'!B24), "")=0, "", HYPERLINK('Sheet 1'!B24, 'Sheet 1'!B24))

This equation will work if I have an e-mail address in Cell B24 and the contents read mailto:aaaaa@yahoo.com.

But it will still not work in the above (Click Here and a hyperlink to C:\Documents and Settings\My Documents\Document1) example, because in the {=Hyperlink(Link_Location, [Friendly Name])} equation the link location refrencing the cell will only work if the cells contents are C:\Documents and Settings\My Documents\Document1.

I want to be able to Type Click Here in Sheet 1 Cell A1 and Hyperlink it to C:\Documents and Settings\My Documents\Document1 then have Sheet 2 Cell A1 contain an equation such as =IF('Sheet 1'!A1="", "", 'Sheet 1'!A1), or however I need to alter it, and the cell with the equation return the {Click Here and a Hyperlink to C:\Documents and Settings\My Documents\Document1}.

I am trying to give you as much detail as I can.

Hi,

Item Count
ABC123 1
ABC123 8
CDE789 3
CDE789 3
ABC123 5

I wanted to sum up item ABC123 = 14
then CDE789 = 6
My item in column 1 might not be sorted ascendingly. Please help. I use vlookup but how can I sum it to get the count?

/m1988

Hi Kristina,

Not sure exactly what you want. I suppose you want the cell on sheet1 to become a hyperlink based on the contents of the cell in sheet2. If so, =hyperlink(sheet2!A1) ought to do it.

If not, give out some more detail.

/m1987

Ok Richard. I suggest you start by using the macro recorder. Set the macro recorder going and then carry out the steps you want to automate. When you have completed the process, stop the recorder. Now you will have a routine to run when you want to update the other sheets.

Try this and let us know how you get on.

I am having problems with a command in Excel. I have Worksheet A referencing Worksheet B Cell, whereby Worksheet B cell is a hyperlink. The command pulls through the cell value only and not the Hyperlink. Is there a way that I can pull through the Hyperlink as well.

Thank You,

Hi Richard

Thanks for comming back. As for Macro's not very good but, I can give it a go!!

Regards Richard M
>
< replied-to message removed by QT >

/m1985

Hello Richard

I don't think you are going to achieve this using formulae. How are you at creating macros?