Formula not calculating if blank cells

[SaraWitch]

Hello peeps,

I am at an impasse with this and am hoping someone can help.

I have a formula that is based on multiple cells. This is working if there is a value in every relevant cell. However, I need it to work even if there are blanks. I have replicated in a test and this is working fine - just not the formula in the main area of the worksheet.

It would be too lengthy to list the issue details here (and besides, on paper, it works!), so, I have attached a link to the actual spreadsheet.

EA Placements Manager

The problem is on worksheet 'EA Placements' and the column in which the formula is not behaving is GY (from row 8). I have laid out a test on columns GX - HK, row 1.

Fresh eyes and any help would be very much appreciated

 

[Fluff]

How about

=LET(f,WRAPROWS(FILTER(C8:GX8,($C$6:$GX$6="weekly true cost")+($C$6:$GX$6="No. of nights in fiscal year")),2),SUMPRODUCT(TAKE(f,,1)/7,TAKE(f,,-1)))

 

[SaraWitch]

Oh wow! I can normally read formulas (even if I can't write them!) but this is way over my head! It works perfectly, thank you so much.

However, I now need to do the same for the other total columns (and have just realised I need to add number of nights to the HB section). If I copy this formula and update the column headings, is there anything else I need to change? I think there is but don't understand what the text in red relate to:
=LET(f,WRAPROWS(FILTER(C8:GX8,($C$6:$GX$6="weekly true cost")+($C$6:$GX$6="No. of nights in fiscal year")),2),SUMPRODUCT(TAKE(f,,1)/7,TAKE(f,,-1)))

 

[Fluff]

The f is just a variable that holds the result of the wraprows/filter formula.
The ,2 is telling the wraprows function to to wrap the result of the filter into two columns.
The Take function takes the 1st column & divides by 7 & the multiplies that by the last column.

 

[SaraWitch]

Ah, that's grand, thank you, @Fluff.

However, because I have to do the same for the other total columns (and I've had to add number of nights to the HB section), it's now showing as #VALUE! (new link below). And the figure in HV9 should be £0.

EA Placements Manager

I still can't quite get my head around how your formula works in order for me to tweak it!

Just out of interest, why don't my original formulas in HU8 and HV8 work if there are blank cells when it's working in the test?

 

[Fluff]

That link is coming back as deleted.

 

[SaraWitch]

Updated workbook:

EA Placements Manager

 

[SaraWitch]

p.s. and so why does the subsidy formula work (cell HX8), which is based on the same principles?!?

 

[Fluff]

For HV8 use

=LET(f,WRAPROWS(FILTER(AE8:CN8,($AE$6:$CN$6="weekly true cost")+($AE$6:$CN$6="No. of nights in fiscal year")),2),ff,IF(f="",0,f),SUMPRODUCT(TAKE(ff,,1)/7,TAKE(ff,,-1)))

and for HW8 use

=LET(f,WRAPROWS(FILTER(DC8:FE8,($DC$6:$FE$6="HB Amount Awarded")+($DC$6:$FE$6="No. of nights in fiscal year")),2),ff,IF(f="",0,f),SUMPRODUCT(TAKE(ff,,1)/7,TAKE(ff,,-1)))

The other option is to use your existing formulae, but change the formulae in AH, AK AS, AV etc to return 0 rather than "" as that is what is causing the problem.

 

[SaraWitch]

Wow. Just wow! I really do appreciate the explanations too - thank you, @Fluff. Always learning thanks to the masters like you

Very last thing, I have tried to amend to calculate from a cell that has a nightly figure, so don't need the divide by 7 (formula in HZ8). So, I just need the first column to multiply by the second column. I tried tweaking as below, but it is calculating incorrectly (it is returning a figure but the wrong one).

=LET(f,WRAPROWS(FILTER(AC8:CO8,($AC$6:$CO$6="Nightly Cost")+($AC$6:$CO$6="No. of nights in fiscal year")),2),ff,IF(f="",0,f),SUMPRODUCT(TAKE(ff,,1),TAKE(ff,,-1)))

EA Placements Manager