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

 

[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

 

[Fluff]

Your Nightly Cost headings have a line feed rather than a space, try

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

 

[SaraWitch]

I offer deepest, sincerest thanks, @Fluff. This thread has truly blown my mind and enhances my learning via the Greats such as you! That's a bit gushy for a Thursday evening, but heartfelt!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[SaraWitch]

Hi there,

I just need to tweak one of these formulas and am getting into a muddle.

The existing formula, which works by multiplying a nightly amount by the number of fiscal nights is:

=LET(f,WRAPROWS(FILTER(AG8:DR8,($AG$6:$DR$6="Nightly Contribution")+($AG$6:$DR$6="No. of fiscal year nights")),2),ff,IF(f="",0,f),SUMPRODUCT(TAKE(ff,,1),TAKE(ff,,-1)))

However, a total amount will now be entered, rather than a nightly one, so I need this total figure divided by the "Number of Nights" and then multiplied by the "No. of fiscal year nights". I have tried, among others and without success:

=LET(f,WRAPROWS(FILTER(AG8:DR8,($AG$6:$DR$6="Final Contribution")/($AG$6:$DR$6="Number of Nights")*($AG$6:$DR$6="No. of fiscal year nights")),2),ff,IF(f="",0,f),SUMPRODUCT(TAKE(ff,,1),TAKE(ff,,-1)))

Any advice would be appreciated

 

[Fluff]

Can you post some sample data.

 

[SaraWitch]

Of course:
EA Placements Manager

Formula in question is in JB8 (I intend to replicate it for JC8 ("Service Charge Paid"))...