Visible Cells help

[Terb123]

Hello,

I'm wondering if someone could help me, please?

I've searched on Google and can't find the answer lol

I have an Excel sheet that calculates my sales, profit, R.O.I, etc)

I have applied a filter to a column, and have used SUBTOTAL to the cells so that the values change depending on the visible data.

The problem I'm having is my R.O.I. I would like the R.O.I to show the percentage depending on the date range.

I can't use SUBTOTAL though as I'm using division to workout my R.O.I.

Does anyone know a formular I can use that will divide the 2 values (total cost-total profit) but only on the visible cells?

Many Thanks

 

[Anonymous1378]

Hard to say without your data but I'm going to guess it's something along the lines of an array formula (Ctrl-Shift-Enter) like:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(Table1[[#Headers],[cost]],ROW(Table1[cost])-ROW(Table1[[#Headers],[cost]]),0,1)),(Table1[profit])-Table1[cost]/Table1[cost],""))

 

[Terb123]

Thanks for the reply. My data is C1 = cost and D1 = profit. My R.O.I
percentage is in E1. Thank You