Problem with formula

**Neales** · 06-30-2008, 03:58 AM

Here's the spreadsheet http://www.mediafire.com/?zsqtojgcynx

Can anyone figure out why some cells in column U on sheet ‘Analysis’ are returning the answer as #VALUE!

I think its something to do with the last part of the formula.

**Simon Lloyd** · 06-30-2008, 04:03 AM

Neales plese upload your workbook here by using Manage Attachments at the bottom of your new post window.

**mudraker** · 06-30-2008, 04:14 AM

whats with the 20 files within the zip file

**Neales** · 06-30-2008, 04:15 AM

The file size is more than 1mb. Its approx 2.25mb

**Simon Lloyd** · 06-30-2008, 04:16 AM

We don't need the entire file just the sheet(s) your concerned with it just has to be a sample!

**Neales** · 06-30-2008, 04:22 AM

I've tried to take out the other sheets but its still over 1mb. The formula links in with the other sheets anyway though.

**TheNorm** · 06-30-2008, 04:38 AM

Try posting the formula that's giving you a problem - we might be able to help from that alone.

**Neales** · 06-30-2008, 04:43 AM

=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))))

**Simon Lloyd** · 06-30-2008, 04:45 AM

You should state that you are using xl2007, however your problem with the formula is after your "/" try using the Formula evaluate function in xl2007

**Neales** · 06-30-2008, 04:49 AM

sorry forgot to mention that. Only just upgraded to 2007 and still getting used to it.

The formula returns the value that I want to divide but won't perform the last part after the "/" like you say

**Simon Lloyd** · 06-30-2008, 04:53 AM

Th value that you want to divide seems to be 5.12 is that correct?

**Neales** · 06-30-2008, 04:59 AM

**EDIT**

The amount to be divided depends on the cell in question. For example in cell U23 the value would be £220.18.

If I delete the bit in bold in the formula below then that value above is returned.

=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))))