Formula returns #VALUE! for some cells

[Neales]

Well this is going to be hard to explain without showing the spreadsheet.

Basically I have a formula

=IF(O23="","",IF(SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959),SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959)*R23,SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$L$2:$L$19959))/SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$J$2:$J$19959*4.33))))

For some reason it is returning #VALUE! in some cells. I think its something to do with the last part of the formula where it is bold.

The formula as you can see looks at data and matches relevant data to return specific depending on what has been matched. Now at the end it is matching the correct data and return that value however i want to divide the value by another (another value*4.33).

Can anyone see any problem with that formula?


<!-- / message -->

 

[Guanjin Peter]

if the returned value is a number....I would suggest a sumProduct formula...it's crazily huge!

 

[Neales]

Yeah it is quite a long formula. It has a lot of criteria to look at though to return the correct value. There is a sumproduct in there and thats where the problem appears to be.

 

[Stormseed]

Hi Neales

Welcome to MrExcel.

I do see that you have a huge formula in your worksheet which rather makes it very difficult and complex for the users and members to read & understand the same.

I would suggest you to try rectifying your formula by breaking it into smaller fragments. Try to calculate each and every part of the formula individually and probably you will get to know where the problem actually exists in your formula. Once you find out the problem and rectify it, you can then merge the formulas together again to accomplish your need.

Do this and even if you do not succeed, you can always post your question on this board clarifying what you need to accomplish in Excel. In other words, your question with full details and your desired answer.

 

[Neales]

Hi thanks for the reply.

I have done what you have suggested already. I know the problem with the formula is from the "/" onwards.

If I delete the part I highlighted in bold from my first post the value that I wish to be divided is displayed. So the problem is that the forumla wont calculate the figure to divide this by.

To do that the formula has to match several criteria to find a value and then multiply this by 4.33. Then this figure should be used to divide the value given by the formula before this point.

So if I give you all an example.

Cell U23 with the current formula displays #VALUE!. If I use the formula before the "/" which would be

=IF(O23="","",IF(SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959),SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959)*R23,SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$L$2:$L$19959))))

then the value 220.18 is returned. Now the last part of the formula missing from that above looks at certain criteria to to return a figure that can be multiplied by 4.33. The formula then divides the original value of 220.18 by the latter value to give the answer.

 

[Stormseed]

Click here to download the HTML Maker which would help you to display your Excel worksheet on the Board:

http://www.mrexcel.com/forum/showthread.php?t=89356

Perhaps, you could explain it in more detail about the issue that you are experiencing ? We can take a broader look at your problem

 

[Neales]

Can I not post up the link to download the whole file? The forumla has links between various sheets.

 

[DonkeyOte]

have you tried encasing the sumproduct prior to multiplication by 4.33 ?

 

[Neales]

Yeah I've tried that

 

[DonkeyOte]

the only distinction between numerator and denominator is the summation column, correct (J) ? All other denominator columns referenced are also referenced in the numerator and if the numerator works correctly then it follows that the denominator equivalents will also work correctly so thefore the issue must be in J, no ? Are all your values in J range numeric ?