Conditional Function Help

**oyz79** · 03-04-2013, 12:28 AM

Hi, I have a spreadsheet which occasionally has cells with empty data. The spreadsheet is downloaded daily from an online table so I don't always know where the empty cells are placed (see column A in attached example). I need to write a formula to calculate return data in column B. So far, the best I have is =IF(AND(A3<>"",A2<>""),A3/A2-1,"") but this does not generate the desired result.

I need help with the first part of the formula. For example, if cell A4 has data but A3 does not have data, I then want the formula to use A2 to calculate returns. For example, (A4/A2-1,""). If A3 has data, then the formula should read (A4/A3-1,"")

**Hawkeye16** · 03-04-2013, 12:42 AM

I would write a function for something like this personally and do it with code (would be cleaner)

For your regular if statements you would need to have each row be different (longer) than the proceeding one.

Please Login or Register  to view this content.

The function I would use is as follows. Then you can just type =FindReturn('cell you want') in the workbook.

Please Login or Register  to view this content.

**oyz79** · 03-04-2013, 01:23 AM

Thanks for the reply, I actually created a work around that has been working by creating a 3rd column, =QUERY( A2:A ; "select * where A is not null limit 21" ) and then I get 21 cells with data, and I can run my return formula in a column C