Average of absolute values greater than 1 or less than -1
1.20 -1.30 0.90 1.00
-2.30 -0.70 2.40 -0.80
I need a formula that will average the absolue values in a table that are either above 1 or below 1. For example, when evaluating the table above, the output would be 1.80 since it would average:
1.20, 1.30, 2.30, and 2.40
Thanks
-2.30 -0.70 2.40 -0.80
I need a formula that will average the absolue values in a table that are either above 1 or below 1. For example, when evaluating the table above, the output would be 1.80 since it would average:
1.20, 1.30, 2.30, and 2.40
Thanks
A3 = IF(ABS(A1)>1, ABS(A1), 0) B3 = IF(ABS(B1),>1, ABS(B1), 0) and so on.
Make an ABS row for each numerical row and then average the ABS rows. Values less than 1 become 0.
That should work.
Make an ABS row for each numerical row and then average the ABS rows. Values less than 1 become 0.
That should work.
ASKER
Create an alternate cell for each cell in the table? The tables I'm working with have 30 columns and 19 rows...
You need to store the intermediate value of your calculation. Make a copy of the table in a separate area and do it that way.
I don't know of any way to make calc1, hold the value, calc2, change and hold the value etc. and add it all up.
I don't know of any way to make calc1, hold the value, calc2, change and hold the value etc. and add it all up.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Saurabh,
Interesting math. I can't comprehend it, but it appears to work!
Mark
Interesting math. I can't comprehend it, but it appears to work!
Mark
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mark,
The logic is simple the first statement acts as a Boolean which is true or false ..Now i multiply that with the next logic where the statement conditions are meet and then add it as one to create a logic of numerator and divided by total number of instances of it which is nothing but basically count..
so =sum/count gives you the average basically what you are looking for..
Saurabh....
The logic is simple the first statement acts as a Boolean which is true or false ..Now i multiply that with the next logic where the statement conditions are meet and then add it as one to create a logic of numerator and divided by total number of instances of it which is nothing but basically count..
so =sum/count gives you the average basically what you are looking for..
Saurabh....
ASKER
sktneer's formula was a bit more elegant in that it requires only 3 cell range changes. Thanks, John, for your input but your replies did not address my request for a formula to do the calculations.
ASKER