Return If Not Blank Question

GoGophers22

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")

Paul Newcome

Are you able to provide some screenshots with manually entered data to show a few examples of what you are trying to accomplish?

Paul Newcome

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)

Paul Newcome

Are you able to provide some screenshots with manually entered data to show a few examples of what you are trying to accomplish?

GoGophers22

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.

GoGophers22

Also added a IRB approval year column to parse out the year in case that was the problem.

Paul Newcome

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?

GoGophers22

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)

Paul Newcome

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)

GoGophers22

THAT WORKED! You are amazing - thank you so much!!

Paul Newcome

Happy to help. 👍️

GoGophers22

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)

Paul Newcome

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, ...............................................))

GoGophers22

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!

Paul Newcome

Try removing the quotes from around 2017.

When you say "no luck" are you getting an error or an unexpected result?

GoGophers22

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

Paul Newcome

Are you able to provide a screenshot of the formula actually in a sheet similar to the screenshot below?

GoGophers22

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.