Trend function skipping NA values (whole office stumped)

[jstone12]

Hello,

I am trying to write a trend function with three pieces of data. Company A revenue (which is one fixed value), other company revenues (array and x variable) and other company bonus levels (array and y variable).

The thing is for some of y variables, I have dashes/blanks. Which cell in the y range is not always the same. Trend does not work with some blanks in the data. I can't treat these values as zero or it throws off the "trend" result.

So I need a formula that I can use to run the trend that excludes the y value that has no value (and its corresponding x) from the trend calculation. Please help, needed ASAP. I would do this manually, but I need to do it for many many sets of data.

So my formula is roughly =trend(company bonuses, company revenues, company A revenue).

I need a formula that works when some random cell in the y range is blank; all the other cells in the X range have values.

Help! Thanks.

[day92]

Hi Jstone and welcome to the forum. It might help if you upload some sample data with expected results.

Go to the advanced tab and manage attachments.

[jstone12]

Thanks day92,

Just figured it out though:

=TREND(IF(C3:C16<>"- -",C3:C16,AVERAGE(IF(C3:C16<>"- -",C3:C16,""))),IF(C3:C16<>"- -",B3:B16,AVERAGE(IF(C3:C16<>"- -",B3:B16,""))),B19,1)


The key is using averages to replace the blanks so the trend result is not modify. Thanks!

[shg]

Replacing the missing values with the average will definitely affect the result.

Try plotting the data with #N/A for the missing values, add a linear trendline, and compare with your results.

[jstone12]

I did, and as long as you replace both the y value and corresponding x value with an average of each respective data set you get the same result as excluding the row all together, which is what I was after.

[Cutter]

@ jstone12

Based on your last post it seems that you resolved your issue but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.