Conditional or If Statement

[TKC100]

I down load a CSV file with Download and Upload speeds. I would like to be able to compute an average upload speed and an average download speed.
The file that I download is formatted such that column B read UP DN alternately down the entire column. I would attach an example but for some reason I'm not allowed. Column C is the size of the test file and column D is the actual download or upload speed expressed as a three to four digit number. Normally I would just do =AVERAGE but I need some way to sort conditionally so that I only average UP or DN from column B.
I would like to have a formula that would calculate an average for all the UP and then all the down without have to rearrange to format of the original CSV file. That is because it is quite large and is constantly being updated.
I really hope this makes sense. I think it would be very obvious it you could just see an example of the sheet.
Any help that you can provide will be greatly appreciated
Thanks!

 

[hockey1859]

TKC,

What you want to use is the =averageif function.

Syntax: =Averageif(average range, criteria range, criteria)

for the upload averages, you want to do =Averageif(D:D,B:B,"UP")

for the download averages, it's the same thing, but change up to down:
=Averageif(D:D,B:B,"DN")

Hope this helps.

 

[TKC100]

Thank so much for your assistance hockey1859 I work with Excel just enough to be dangerous.
I copied your suggestion directly into the sheet =Averageif(D:D,B:B,"UP") but I receive the error message #NAME?
The sheet appears like this
Column B UP DN UP DN UP DN……..
Column D 234 864 123 648 68 348
I would like to be able to answer the following queries:
Average upload speed =
Average download speed =
Without have to modify the sheet itself.

 

[T. Valko]

Thank so much for your assistance hockey1859 I work with Excel just enough to be dangerous.
I copied your suggestion directly into the sheet =Averageif(D:D,B:B,"UP") but I receive the error message #NAME?
The sheet appears like this
Column B UP DN UP DN UP DN……..
Column D 234 864 123 648 68 348
I would like to be able to answer the following queries:
Average upload speed =
Average download speed =
Without have to modify the sheet itself.

What version of Excel are you using?

AVERAGEIF requires Excel 2007 or later.

 

[gehusi]

The formula is incorrect - it should be

=averageif(range, criteria, average range)

So in this case you'd want:

=averageif(B1:B10,"UP",D1:D10)

 

[gehusi]

Although as T. Valko pointed out, if you are getting a name error you are probably working with an earlier version of Excel which does not have an AVERAGEIF function. If this is the case, you could instead use:

=sumif(B1:B10,"UP",D1:D10)/countif(B1:B10,"UP")

 

[T. Valko]

The formula is incorrect - it should be

=averageif(range, criteria, average range)

So in this case you'd want:

=averageif(B1:B10,"UP",D1:D10)

That may be so but when entered as such:

=Averageif(D:D,B:B,"UP")

Excel doesn't know that the ranges are reversed and that is not why the result is the #NAME? error.

 

[Ruddles]

I would attach an example but for some reason I'm not allowed.

You can't attach files to messages but if you place a border around your worksheet cells and select a fairly small font (8pt works well), you can copy and paste small sections of worksheet directly into a message.

 

[TKC100]

You are absolutely right T. Valko I am using Excel 2002 or XP.
You too are right gehusi your work around work quite well.
Now I am going take Ruddles suggestion
A B C D
Date Type Test Size Test Score (in Kbps)
Fri Jun 17 UP 193 MB 143
Fri Jun 17 DN 2 MB 834
Thu Jun 16 UP 193 MB 120
Thu Jun 16 DN 4 MB 1346
Wed Jun 15 UP 193 MB 138
Wed Jun 15 DN 4 MB 859
Wed Jun 15 UP 193 MB 199
Wed Jun 15 DN 4 MB 376
Tue Jun 14 UP 193 MB 154
Tue Jun 14 DN 4 MB 898
Tue Jun 14 UP 193 MB 135
Tue Jun 14 DN 4 MB 802
This is an abbreviated view of my worksheet
I am now able to get an average UP and an average DN

Has anyone got an idea how to get
Max DN and Min down from the same sheet?

I can't begin to tell you all how grateful I am for you support. I could/would have spent day coming up with a workable solution.
Thanks!

 

[TKC100]

The worksheet columns look way better before I submitted the reply.
Not sure what I did wrong