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