Formula returns #VALUE! for some cells

Neales

New Member
Joined
Jun 30, 2008
Messages
8
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 -->
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Guanjin Peter

Active Member
Joined
May 21, 2008
Messages
429
if the returned value is a number....I would suggest a sumProduct formula...it's crazily huge!
 

Neales

New Member
Joined
Jun 30, 2008
Messages
8
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

Banned
Joined
Sep 18, 2006
Messages
3,274
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

New Member
Joined
Jun 30, 2008
Messages
8

ADVERTISEMENT

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.
 

Neales

New Member
Joined
Jun 30, 2008
Messages
8

ADVERTISEMENT

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

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
have you tried encasing the sumproduct prior to multiplication by 4.33 ?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,568
Members
414,079
Latest member
Frills

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
Top