DifferenceIF

[Stryfe]

I am calculating the last 20 data points in column H. Column H currently has a formula in it which is (=G1-F1) and this runs down the entire column of H for it's corresponding cell. My problem is another section averages the last 20 data points which works fine however column H is now full all the way down with zeros if the corresponding columns of F and G are blank. This would be simple with a SumIf function but I am needing the difference and excel does not have a differenceIf.

Currently unable to put up a spreadsheet example but pretty straight forward.

Thank you for any help

[PaulM100]

Maybe, in H:

=IF(OR(G1="",F1=""),"",G1-F1)

[sandy666]

Maybe PivotTable will do that?

without example excel file this is just guesses

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Stryfe]

My apologies for not being to add an example at the time but PaulM100 crushed it for me so thank you Paul. I compared my existing 3K data points old formula to his modified version and got the same results plus it allowed my average formula to be correct.