Formula not calculating if blank cells

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
378
Office Version
  1. 365
Platform
  1. Windows
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 :)
 
Your Nightly Cost headings have a line feed rather than a space, try
Excel Formula:
=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)))
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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! :giggle:
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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:
Excel Formula:
=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:
Excel Formula:
=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 :)
 
Upvote 0
Thanks for that, how about
Excel Formula:
=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")),3),ff,IF(f="",0,f),SUM(INDEX(ff,,3)/INDEX(ff,,1)*INDEX(ff,,2)))
 
Upvote 1
Absolutely spot-on!! I would never have got there on my own - thank you so much @Fluff. And... ...this has really helped me understand the formula better, so double-thanks! :biggrin:
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top