Is there a way to calculate the average of only values that are > 0, and separately only values that are <0, in a list of 500 values that contains both positive and negative values?
p.s. I'd prefer not to break the data out into additional columns because the spreadsheet is already quite large
many thanks,
=AVERAGEIF(A:A,">0",A:A)Is there a way to calculate the average of only values that are > 0,
What do you mean when you say separate the negative values but you don't want an extra column?..and separately only values that are <0, in a list of 500 values that contains both positive and negative values?
p.s. I'd prefer not to break the data out into additional columns because the spreadsheet is already quite large
Don't you need a list with all negative values?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
I don't know where your data is, but you can run this macro to find both averages.
Please Login or Register to view this content.
~~LaffyAffy13~~
If I have helped you solve your problem, please be sure to clickat the bottom left of my post.
Thanks.
Fotis, I don't believe they want to separate the values, I believe they want to find the average of the negative values, separately. Also, use Fotis's method, its way easier hahaha
I used 'average' to keep my question simple, but I'm actually looking to calculate the covariance and variance for values >0; and values<0. Is there an equivalent 'averageif' function for covariance or variance? My cell formula looks like this:
I don't want an extra column to for efficient data management.... there are already more than 100 columns in the spreadsheet, and I'd prefer not to double or triple the number of columns by separating positive and negative values for each column. ThanksPlease Login or Register to view this content.
As i don't have any more free time, i'll not be able to continue on this today. BUT even if i had(time) i am not able to understand your goal, without a small sample workbook.
Sometimes, adding an extra helper column or 2 can in fact be more efficient/faster than creating a hulking big, involved formula. Im not saying that is is (or is not) 1 of those situations, just that you should keep your options open
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
beta.xlsm
This macro will work for 1 column of data, but the actual problem has about a 100 columns. I've attached the file... essentially I'm trying to calculate all values in rows 35 to 52, but with only positive values (and will do it again separately for only negative values). Any ideas appreciated!
Thanks, Don
When I load your file, ALL i get is errors from D:CJ
Re: calculate average of positive values only
beta2.xlsx
I resaved as an excel workbook (instead of macro-enabled)... maybe this will help. it works on my machine and I'm using MS Office professional 2010.
its not the macro-enabled part, you are using a function that 2007 does not recognise
Perhaps 2007 doesn't recognize the 'index function' ? The formula in the cells from D:CJ is essentially this:
Please Login or Register to view this content.
no, its the _xlfn.COVARIANCE.P() part
the formula itself is irrelevant to your question here, so maybe you could just value those answers and upload again? (save as a COPY so you dont mess up your file!!!!)
beta3.xlsx
I've greatly simplified the file/formula and re-uploaded. If this doesn't open correctly let me know. Hopefully this helps conceptualize the issue.
Thanks!!
perhaps this...
=AVERAGEIF($D$18:$D$34,">0") returns 0.0133493731922283
and
=AVERAGEIF($D$18:$D$34,"<0") returns -0.0229242524337096
that works for calculating the average... is there an equivalent or other creative approach to do the same for calculating the variance or covariance?
Don
that works for average... any suggestions for doing the same when calculating variance or covariance?
Don
you need an array formula like
=VAR(IF($D$18:$D$34>0,$D$18:$D$34))
entered with ctrl+shift+enter
Josie
if at first you don't succeed try doing it the way your wife told you to
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks