Using data found in columns DATA N, DATA R, & DATA S of the attached workbook, I would like to determine the value in cell RESULTS I6.
Basically, I want to know the average Max % Gain with M Cap < 100M & Float < 1M for cell RESULTS I6.
I've tried... {=AVERAGE(COUNTIFS(DATA!N:N,DATA!R:R,"<=100000000",DATA!S:S,"<=1000000"))}
Last edited by cableghost; 06-23-2021 at 11:52 AM.
Delete, not appropriate.
Last edited by josephteh; 06-23-2021 at 01:33 AM.
Try
in I6
=IFERROR(AVERAGEIFS(DATA!N:N,DATA!R:R," < 100000000",DATA!S:S," < 100000000"),0)
in I7
=IFERROR(AVERAGEIFS(DATA!N:N,DATA!R:R," < 100000000",DATA!S:S," >=100000000",DATA!S:S," < 500000000"),0)
Repeat for other ranges.
Thanks JT.
The following does not work for cell RESULTS M6. By my count, there are 10 rows that match these conditions.
Edit: It works... I neglected to remove the space in front of the <> signs.
Please Login or Register to view this content.
Last edited by cableghost; 06-23-2021 at 11:51 AM.
Remove leading/trailing blanks before the ">" and "<" These were added to get over the "security" on the forum.
I got an answer of 57%
Thanks. Yep, I had figured that out just prior to your reply.
What if I wanted to add another condition... I only want to consider a calculation if there are at least 5 results, otherwise 0. I assume I would add in a COUNTIF here?
Without checking add COUNFIFS: you can use the criteria from AVERAGEIFS ......
Untested ....(embedded spaces again because of firewall)
Formula:
Please Login or Register to view this content.
I receive an error... Too few arguments for this function.
Formula:Please Login or Register to view this content.
Great thanks! I see adding the additional argument of ,"".
One more twist to this... iferror enter 0 is not working. If the condition of '>=5' is not met, the cell is blank. I guess this is ok... better than an DIV error showing.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks