Maybe a loop?

Tucker92

Board Regular
Joined
Jun 7, 2018
Messages
53
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
 
Last edited:

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
711
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

Board Regular
Joined
Jun 7, 2018
Messages
53
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
 
Last edited:

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
711
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.
 

Forum statistics

Threads
1,077,650
Messages
5,335,493
Members
399,019
Latest member
prab1101

Some videos you may like

This Week's Hot Topics

Top