Excel Formula

  User-99CC659A-9EBD-457D-A9DFF0C4026F4216 21:39 27 Mar 2006
Locked

Hi,
I'm hoping that someone with a lot of experience with excel and formula's is reading this and can help me out.

I have a spread sheet which tracks various pieces of data on one tab and then does summaries based on month, transaction type and time taken on another tab. At the moment in order to update the second tab I have to manully work out the figures and update the spreadsheet. Does any body know of a formula which will allow me to do say if column A = X and column B = Y then work out the average of column C?

Any and all help would be greatly appreciated.

Regards

  VoG II 21:44 27 Mar 2006

Is column C always the same length (i.e. number of Rows)?

What would X and Y be - numbers, text, Boolean, dates, ...

Where does this formula go?

Basically, a lot more information is required.

Would a UDF be OK?

VoG, X is Month and Year, Y is set text, C is date and time in minuets. This formula would be part of the same worksheet but is on a differnt TAB.

The problem that I have is that the amount of data (number of row's) varies from month to month and the data that I need to analyze is mixed up with other data. At the moment I have to filter X by Month and Year and then filter Y by the selected text. Then I can use the average function in excel to see what the the average is based on my filter. I want a formula that will automatically work this out as the data is updated so that I do not need to continually be updating the spreadsheet each time new data is entered in the main spreadsheet.

Does that make sense? if it would help I could email part of the spreadsheet so that you can see what I'm looking at.

  VoG II 21:59 27 Mar 2006

Um - sort of. You have mail.

VOG,
Sorry for the delay in responding. I have sent you an email with part of the spreadsheet attached.

Thanks

  VoG II 13:29 28 Mar 2006

Thanks for the example. I think that the easiest way to accomplish this is to use a set of array formulas. For example the average of the entries in Column I for February would be given by

=AVERAGE(IF(A2:A180=DATEVALUE("Feb-06"),IF(H2:H180="Targetted",I2:I180)))

To enter this as an array formula you type it into the formula box as usual but then press

CTRL + Shift + Enter together.

Excel will then display the formula as

{=AVERAGE(IF(A2:A180=DATEVALUE("Feb-06"),IF(H2:H180="Targetted",I2:I180)))}

You will need to format the cell that this formula is entered in as Time. Also change 180 to be >= the last row containing data.

For the example, this gives a result of 0:08 which looks about right.

Please note that if you edit the formula, you will need to highlight it in the formula box and press CTRL + Shift + Enter to get Excel to treat it as an array formula.

P.S. sorry for the delay - work intervened.

VOG,
Thanks, I was working last night myself. I'm off for a few days now but i will try what you suggest above when I back in the office next week and get back to you to let you know how it goes.

Hi VoG,
Brilliant. It works. Many thanks for all your help.

  Boy Zone 13:14 02 Apr 2006

I noticed this thread, I too had simular problem, and yes VOG came to my rescue.
Who ever VOG is he is very clever.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires