Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average only first number of actual values | Excel Worksheet Functions | |||
How to Search, Count, Match and Post Values | Excel Worksheet Functions | |||
URGENT: Please Advise. SumProduct and Operand Question | Excel Discussion (Misc queries) | |||
SUMPRODUCT on multiple values | Excel Discussion (Misc queries) | |||
Obtaining Max and Min Values with Sumproduct | Excel Discussion (Misc queries) |