Custom average function

[wik]

Excel question. I have data in column F that corresponds to the data I need from Column N. If there is a 1 in column F I know how to select all cells with a 1 in column F. After that I am lost because I need to select all cells in column N that are in the same row as the one that have a 1 in column F. Once you can help me find out how to select just those cells in column N that correspond to a 1 in column F, I need to take an average and find std for the cells that I need selected in column N. Any help?

Also the data sheet is 10,000 or so rows long so how can I highlight all the cells in a column without ctrl clicking and scrolling?

Thanks

[FDibbins]

Hi, welcome to the forum

I think you need to take a look atthe averageif() function, but for better suggestions, attach a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[FDibbins]

deleted forum-induced duplicate thread

[cbatrody]

Hi,

Welcome to the Forum.

Please try the following formula.

=AVERAGEIF(F2:F10000,1,N2:N10000)

[wik]

Hi,

I have attached a dummy workbook that mimics the workbook that I need help on so I can explain what I am trying to do.

Any cell that has a value of 1 in column F is a cell that I need. Once I have selected all rows with a 1 in them for column F, I need to calculate a value using the values from columns N and M that have a 1 for column F. This equation is:
((65535.0/52000.0)*(2000.0 - M VALUE))*(N VALUE). Once I have these new values calculated (for those N and M in rows with a 1 in column F), hopefully in a new column (maybe column O?), I need to take an mean and find the standard deviation of these new calculated values.

Please ask if anything in my question is unclear

Thank you for your help.

dummy average workbook.xlsx

[wik]

Hi,

Thanks for your advice. I have posted a new, updated thread at this link:

http://www.excelforum.com/excel-gene...ml#post3799097

Thanks again

Hi, welcome to the forum

I think you need to take a look atthe averageif() function, but for better suggestions, attach a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[newdoverman]

The enclosed workbook has extracted the values corresponding to 1 in column F from columns M and N and put them in columns P and Q. You can now apply your formula to the extracted values. The second file is an attempt to apply your "formula".

[FDibbins]

I have merged your 2 threads, please stick with 1 thread until you have your answer