Maybe a loop?

[Tucker92]

Hi everyone,

I was wondering if there was a way that i would be able to add up some numbers based on whether or not there was a letter in the cell below the cell i want to include.

Currently i have a range of numbers but i need to be able to mark them in some way if they are above or below the average, this will be done manually and i was after a loop or something to go through my data and each time it finds a character record the number above and total them together into a cell i choose.

Is this possible?
Bit of a noob when it comes to VBA

 

[jmacleary]

Hi there. Rather than manually marking up above or below the average, you could do it all in one formula. Put this formula in the cell where you want the above average sum: =SUMIF(A1:H1,">"&AVERAGE(A1:H1))

A1:H1 is the range of values, so adjust to suit. To get below average, use =SUMIF(A1:H1,"<"&AVERAGE(A1:H1)), you may want to put an equals sign in as well (i.e. >= or <=) to catch any exact matches.

 

[Tucker92]

Im happy with the manual input as the numbers are timings and its just going to be the ones that are really far out. I have a timer on a userform that is used as a stopwatch and it will only be when there is an issue in the work area that the number will be far out and will easily be noticed.

I am just after a way of totaling up the marked items to be inputted into the another cell as i need an overall ineffective time of the bad ones

I can use this in another of my spreadsheets and will help alot
thank you

 

[jmacleary]

OK thats fine. In that case, you need this formula in your desired result cell: =SUMIF(A2:H2,"n",A1:H1)
This sums all the numbers in range A1:H1 if the equivalent value in range A2:H2 (i.e. the row underneath) is an n.

 

[Tucker92]

Yeah that worked perfectly thank you

 

[jmacleary]

You're welcome and thanks for the feedback.