SUMPRODUCT rounding up values
 

I am currently using a formula which allocates a pre-designated weight to an
abbreviated text code:

An example of the list on sheet 5 starting in A1 and B1 is:-

SO 36.00
DO 54.00
GH 10.50
HD 8.00
FF 74.00
DW 47.00
WD 92.00
CH 20.00
EH 11.00
WM 78.00
FRE 40.00
FRI 39.50

And the formula I am using on the relevant rows on the other sheets is:

=IF(B6="","",SUMPRODUCT( ( LEN( B6) - LEN( SUBSTITUTE( LOWER( B6), LOWER(
Code), "" ) ) ) / ( LEN( Code) + ( LEN( Code ) = 0 ) ),Weight ))

It works fine apart from it seems to be rounding up the values e.g. 39.50
from the weights sheet appears as 40 on sheet 1.

Can anyone please advise me how I can get the values to show the same value
as they appear in the table against the relevant text reference.

The text codes need to be used in various combinations in the same cell of
the main sheet which gives a cumulative sum total so can appear as wm, dw,
ff, fri€¦.etc

I hope this explains my problem ok.

SUMPRODUCT rounding up values
 

We really need to know what ranges Code and Weight refer to, and what kind of value is in cell B6 of
the sheet where the formula is used.

HTH,
Bernie
MS Excel MVP


"Terranoman" wrote in message
...
I am currently using a formula which allocates a pre-designated weight to an
abbreviated text code:

An example of the list on sheet 5 starting in A1 and B1 is:-

SO 36.00
DO 54.00
GH 10.50
HD 8.00
FF 74.00
DW 47.00
WD 92.00
CH 20.00
EH 11.00
WM 78.00
FRE 40.00
FRI 39.50

And the formula I am using on the relevant rows on the other sheets is:

=IF(B6="","",SUMPRODUCT( ( LEN( B6) - LEN( SUBSTITUTE( LOWER( B6), LOWER(
Code), "" ) ) ) / ( LEN( Code) + ( LEN( Code ) = 0 ) ),Weight ))

It works fine apart from it seems to be rounding up the values e.g. 39.50
from the weights sheet appears as 40 on sheet 1.

Can anyone please advise me how I can get the values to show the same value
as they appear in the table against the relevant text reference.

The text codes need to be used in various combinations in the same cell of
the main sheet which gives a cumulative sum total so can appear as wm, dw,
ff, fri..etc

I hope this explains my problem ok.

SUMPRODUCT rounding up values
 

=IF(C3=1;CHOOSE(C5";1-7";"8-14";"15-21";"22-28");IF(C3=2;CHOOSE(C5;"29-35","36-42";"43-49");""))

Just add to the lists

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Terranoman" wrote in message
...
I am currently using a formula which allocates a pre-designated weight to
an
abbreviated text code:

An example of the list on sheet 5 starting in A1 and B1 is:-

SO 36.00
DO 54.00
GH 10.50
HD 8.00
FF 74.00
DW 47.00
WD 92.00
CH 20.00
EH 11.00
WM 78.00
FRE 40.00
FRI 39.50

And the formula I am using on the relevant rows on the other sheets is:

=IF(B6="","",SUMPRODUCT( ( LEN( B6) - LEN( SUBSTITUTE( LOWER( B6), LOWER(
Code), "" ) ) ) / ( LEN( Code) + ( LEN( Code ) = 0 ) ),Weight ))

It works fine apart from it seems to be rounding up the values e.g. 39.50
from the weights sheet appears as 40 on sheet 1.

Can anyone please advise me how I can get the values to show the same
value
as they appear in the table against the relevant text reference.

The text codes need to be used in various combinations in the same cell of
the main sheet which gives a cumulative sum total so can appear as wm, dw,
ff, fri..etc

I hope this explains my problem ok.