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!

 

[T. Valko]

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!

Try these array formulas**.

For the max:

=MAX(IF(B2:B10="DN",D2:D10))

For the min:

=MIN(IF(B2:B10="DN",D2:D10))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Ruddles]

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

Did you place borders around the cells?

 

[Tigs81]

I would use a DMAX Formula.

Down

=DMAX(B1:D10, "Score (in Kbps)", F1:F2))

then in a column like F1 and F2 enter
F1 - TYPE
F2 - DN

Up

=DMAX(B1:D10, "Score (in Kbps)", G1:G2))

then in a column like G1 and G2 enter
F1 - TYPE
F2 - UP


Thanks
Tigs

 

[TKC100]

T. Valko, Your suggestion worked perfectly and in order to get calculation for the Uploads I just had to change DN to UP
Also I needed to change the columns and row a bit but was surely due to my poor representation of the sheet. I am very grateful for your help. What you did was way out of my league.
Tigs81, I did not try your solution. If is working don’t mess with is my thought but thanks you as well for taking the time to assist me.
Ruddles, No, I did not put individual borders around each cell before I cut and pasted it into the message. I am glad everyone was able to make sense of it in spite of my error.

 

[T. Valko]

T. Valko, Your suggestion worked perfectly and in order to get calculation for the Uploads I just had to change DN to UP
Also I needed to change the columns and row a bit but was surely due to my poor representation of the sheet. I am very grateful for your help. What you did was way out of my league.
Tigs81, I did not try your solution. If is working don’t mess with is my thought but thanks you as well for taking the time to assist me.
Ruddles, No, I did not put individual borders around each cell before I cut and pasted it into the message. I am glad everyone was able to make sense of it in spite of my error.

You're welcome. Thanks for the feedback!