Formula Adjustment - Calculating 8% Thread

Question

Hello,

I am having an issue with my formula below. I have this formula in about 10 columns and it is slowing down the excel file. If I add a row or a value into a cell on the "credit calc - monthly" tab, it takes a while to update and I see at the bottom it states "calculating (8 Threads %). I am not sure how this can be fixed?

=IFERROR(INDEX('Credit Calc - Monthly'!$B$12:$B$975,AGGREGATE(15,3,1/('Credit Calc - Monthly'!$B$12:$B$975<>"")*ROW(B:B),ROW(1:1))),"")

Answer 1

Hi,

In cell D2, enter this formula

=FILTER(A2:A10,A2:A10<>"")

Hope this helps.

Answer 2

You are welcome.

Answer 3

Thank you soooooo much!!! This worked perfectly. ;) :) :) :)

Answer 4

Would you know of another formula I could use? I am trying to achieve the following...

Is there a formula to pull in cells with data and to ignore the blanks?

For example, in the tab labelled "monthly", column A has data in Rows 1-5, 10, 19, 26, 30-35 etc. The other rows in between are blank. I need a formula to pull in the data onto another tab labeled "invoice" where cells with data would pull in and rows with blanks would be ignored. The "invoice" tab cannot contain any blank rows so the formula would need to pull in ex: (rows 1-5 which have data and then pull in row 10 but row 10 data would need to pull into row 6 of the "invoice" tab)

Example:

Data on the "monthly" tab:

Row 1 Fees <-- Header

Row 2 20

Row 3 30

Row 4 60

Row 5 5

Row 6 BLANK ROW

Row 7 BLANK ROW

Row 8 BLANK ROW

Row 9 BLANK ROW

Row 10 45

final result --> What it should look like on the invoice tab:

Row 1 Fees <-- Header

Row 2 20

Row 3 30

Row 4 60

Row 5 5

Row 6 45

Where it ignores the blank rows.

Answer 5

This is to be expected with the formula you have here, it happens often esp. with array formulas and improper use of SUBTOTAL.

You should scrap it and write a different one that will achieve what you desire without calculation issues.

Br,