Return If Not Blank Question
Hi! Overall I want to create a run chart with an average acceptance time over years. I think it might take more than 1 step, but am having trouble with the formula and not sure if it's even possible. Can you return a number when the cell is not blank along with another quantifier like year?
I was trying:
=IF(Elapsed Time]@row <> "", result, ''', (IRB Approval Date]@row, "2016")
Best Answers
-
Are you able to provide some screenshots with manually entered data to show a few examples of what you are trying to accomplish?
-
Try this instead for 2017 and see what that gets you...
=AVERAGEIF([IRB Approval Year]1:[IRB Approval Year]73, "2017", [Elapsed Time]1:[Elapsed Time]73)
Answers
-
Are you able to provide some screenshots with manually entered data to show a few examples of what you are trying to accomplish?
-
I posted it here - I don't think I'm explaining it quite right. I also was playing around with averageif formulas. I thought maybe I'd have to return the number and then do an average - but maybe it can be 1 step?
=AVERAGEIF([Elapsed Time]1:[Elapsed Time]15, ">0", ([IRB Approval Date]1:[IRB Approval Date]15, =2017))
Goal is to be able get an average amount of elapsed days per year for a line graph.
-
Also added a IRB approval year column to parse out the year in case that was the problem.
-
I'm still not sure I follow. Are you able to provide another screenshot as above except with the results manually entered into the appropriate cells so that I can see the end result you are looking for?
-
Absolutely. Again, I don't think I'm explaining myself very well so thank you for bearing with me!!
Above is what I'm going for - this will let me do a line graph to plot the average acceptace time over years. Basically to see if the length of time it takes to 'accept' something is going down or up.
I'm not sure if the average if is necessary. I'm not thinking I just need an average formula with a year quantifier? I tried this and got 0, so not even sure if I'm on the right path.
=AVERAGEIF([IRB Approval Date]1:[IRB Approval Date]73, "*2017*", [IRB Approval Year]1:[IRB Approval Year]73)
-
Try this instead for 2017 and see what that gets you...
=AVERAGEIF([IRB Approval Year]1:[IRB Approval Year]73, "2017", [Elapsed Time]1:[Elapsed Time]73)
-
THAT WORKED! You are amazing - thank you so much!!
-
Happy to help. 👍️
-
Loved it - worked. Now they want to see the average acceptance time by year broken down by different Study Type. I have 6 different drop down options for Study Type and they want 2 combined - Prospective Observational and Clinical Trial. Sheesh! Lol.
I tried just adding in the other column with the study type, but no dice. Any other great ideas? My columns are now, IRB Approval Year, Elapsed Time & Study Type. I'm trying to combine Prospective Observation and Clinical Trial.
=AVERAGEIF([IRB Approval Year]2:[IRB Approval Year]73, "2017", [Study Type]2:[Study Type]73, "Prospective Observational", [Study Type]2:[Study Type]73, "Clinical Trial", [Elapsed Time]2:[Elapsed Time]73)
-
AVERAGEIF can only use one range/criteria set. TO incorporate more range/criteria sets, you will need to switch over to AVG/COLLECT.
=AVG(COLLECT({range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ...............................................))
-
That makes a lot of sense. I tried this, but no luck.
=AVG(COLLECT([Elapsed Time]2:[Elapsed Time]73, [IRB Approval Year]2:[IRB Approval Year]73, "2017", [Study Type]2:[Study Type]82, "Prospective Observational"))
Any thoughts?
Thanks again for all your time and help!
-
Try removing the quotes from around 2017.
When you say "no luck" are you getting an error or an unexpected result?
-
Yes, sorry for not being more specific! I am getting an unparsable error. Also tried without the quotes and still got the error.
Thank you again!!
-Andrea
-
Are you able to provide a screenshot of the formula actually in a sheet similar to the screenshot below?
-
Sure thing, here it is with the quotes. I tried without the quotes, but then it doesn't highlight the cells. Thought this might be easier to see. This way gets me incorrect argument error, without quotes gives me unparsable error.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!