SUMPRODUCT Formula is not working and I cant see what is wrong with it

[necht_angel]

Hi all,

Have been staring at this formula now for hours and I can not see what I have done wrong. Have attached the spreadsheet with further explanation.

Is anyone able to help with how I correct this, it is driving me nuts!

Thanks guys

Kate

[ben_hensel]

what's all that stuff in row 39 or so supposed to be referring to?

I mean,
the boolean test in the SUMPRODUCT is returning
0 - range_elements > 0
which is FALSE for all range_elements since they're a date array.

I don't think there are any problems with the formula, but...
right now, it's, like,
SUMPRODUCT(all_zeros) * 1-0 * 1-0 * 1+0 - SUM(all_zeros)

[necht_angel]

Ah rubbish, sorry, as I had to copy and paste the formula from a bigger spreadsheet I haven't changed all the 39's to 13's. Sorry all 39 should read 13, but it still doesn't work!

[necht_angel]

Have updated the spreadsheet with correct line references! Thanks for pointing that out :-)

[ben_hensel]

K4 = K11

L4 = K4 +1
M4 = L4 +1
etc

G13 = 5

So the boolean test
$K$11 - $K$4:$ER$4 > $G13

will assess as somthing like:
1 - 1:100 > 5

So that's not going to deliver TRUE ever, because it's going negative.

Assume you switch them:
1:100 - 1 > 5

That's not going to deliver TRUE until column Q; every term left of that will be multiplied by zero, not 1.



Also,
That SUM(start:right) term in the end, I don't think that's what you want, because it will subtract the 1st term n times, the 2nd term n-1 times, etc, up to the nth position it's at. I don't think you want that kind of polynomial behavior; I think you just want to subtract the cell to the right.

[XOR LX]

Hi,

Even changing the row to 13, that still doesn't change the fact that none of your conditions in the SUMPRODUCT is TRUE.

For example, taking the formula in P13, you are asking the SUMPRODUCT to sum over a range where the following condition(s) are TRUE:

($K$11-$K$4:$ER$4)>$G13

i.e. which of the dates in K4:ER4, when subtracted from the date in K4, produce a value which is greater than the value in G13?

However, since none of the dates in that range (22-Nov-13, 23-Nov-13, 24-Nov-13, 25-Nov-13, 26-Nov-13, 27-Nov-13, 28-Nov-13 - plus a string of blanks) are greater than that in K11 (22-Nov-13) this part of your formula will only evaluate to a string of FALSEs.

Regards

[necht_angel]

Hi Ben,

Thanks for your reply. Sorry I'm a bit confused. I didn't write the original formula I have reused it from a previous version of the spreadsheet, and I am not very knowledgable when it comes to writing them, so I don't really understand what you have said... sorry!

If it is not going to give me the result I want, would you know how to re-write it to achieve what I want?

[ben_hensel]

...In that case,

I think it would be better to take a step back and ask,

"what do you want it to do???"

[necht_angel]

I want the figure in K6, after the payment due days in G6 (which is 5 in this case), to arrive in row 13, 5 days later, less the 5% in d13, 2.5% in e13, add the 20% in f13, so basically arrive in cell p13 as the figure 111.00

Basically I am putting on the invoices in the top lot of rows, and in the rows underneath based on payment days and discounts want the cash amount to drop in when it's due :-/ is that even possible then?

ahhh, so confusing!

[ben_hensel]

So really, you want TWO different operations:

1) Determine which row to operate on based on the date

2) Operate on them based on the percentages

We'll do (1) using some INDEX(output_array, MATCH(current_date - days, date_range, 0)) ; once you got that, it's practically trivial to do (2).

I also wrapped an IF to handle errors as zero, rather than throwing errors.

Result:

Please Login or Register  to view this content.

pull across
P13 = 111.15
Q13 = 555.75

...
This is just my opinion, but the way you've got this layed out looks really strange to me. I dunno how you're using it, but if I was you, I would think about re-organizing.

[necht_angel]

Hi Ben, that actually works pretty perfect. Thank you so much.

The layout is not the final layout, it's just a sample from a much bigger sheet.

Your a star :-)

Kate