Referenceone sheet and if 0, reference another sheet

NateP

Hello, I've been using smartsheets for several years now but really only starting to use formulas until recently so forgive my lack of knowledge.

I have three sheets in play here. The primary sheet is looking at the second sheet for an average based on all references of a SKU. The second sheet might not have data for a particular SKU, so it shows 0 in my primary sheet. The third sheet is actually an archive of the second sheet for old data which is moved by automation once it's a certain age.

I want the formula to first look at the second sheet because it's data is more relevant and not outdated, but if there is no data (value of 0), I want it to look at the third sheet as a backup until the second sheet gets data greater than 0.

The formula I have that references the second sheet is: =AVERAGEIF({Part # reference 1}, SKU@row, {Assembly Reference 1})

The formula for the third sheet is: =AVERAGEIF({part # reference archive}, SKU@row, {assembly reference archive})

Each of the references are pointing to the correct sheets even though their names are similar. Both formulas on their own return values and work.

Now I just want to combine the two formulas to look at the second sheet first and then if 0, reference the third sheet. I thought something like the formula below is on the right track but since I am new to formulas, I'm still trying to figure out how to correctly write them. Usually I'm missing a parenthesis somewhere or a comma, but this is one of the more complex ideas I've done and can't seem to find a solution online. I get an Unparseable error with the below formula and I don't actually know what that means.

=AVERAGEIF(IF(({Part # reference 1}, SKU@row, {Assembly Reference 1})>0,({Part # reference 1}, SKU@row, {Assembly Reference 1}), ({part # reference archive}, SKU@row, {assembly reference archive})))

Paul Newcome

Give this a try...

=IF(AVERAGEIF({Part # reference 1}, SKU@row, {Assembly Reference 1}) <> 0, AVERAGEIF({Part # reference 1}, SKU@row, {Assembly Reference 1}), VERAGEIF({part # reference archive}, SKU@row, {assembly reference archive}))

Paul Newcome

Happy to help. 👍️

And sorry about that typo. Fingers and brain aren't always in sync. Haha

Paul Newcome

Give this a try...

=IF(AVERAGEIF({Part # reference 1}, SKU@row, {Assembly Reference 1}) <> 0, AVERAGEIF({Part # reference 1}, SKU@row, {Assembly Reference 1}), VERAGEIF({part # reference archive}, SKU@row, {assembly reference archive}))

NateP

https://community.smartsheet.com/discussion/comment/317806#Comment_317806

That worked...I just had to add an A to VERAGEIF in your example above. Thank you so much.

That just helped so much. All those years of collecting data with no real purpose helped fill in all the gaps with this formula.

Paul Newcome

Happy to help. 👍️

And sorry about that typo. Fingers and brain aren't always in sync. Haha