• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Product of digits to the left & right of the decimal point

C

clumpa

Member
I have a column of decimal numbers and firstly want the product of the integer & decimal values. e.g. 2.3 produces 6 as does 3.2. I then want the maximum value of the product to identify which rows it occurs in.
The problem that I have is that a formula such as
=IF(A1=MAX(A1:A12),B1,"") where column A contains the product value, the formula only returns a value from column B where the starting decimal value is highest
e.g. 3.2 returns a value from B and 2.3 does not
I have tried various ways of calculating the product value
The product values that are "ignored" are omitted by COUNTIF formula but are included by SUM
 
@clumpa,
To get the product, you could try something like:
=MID(J10,1,FIND(".",J10)-1)*MID(J10,FIND(".",J10)+1,255)
where J10 has the number

I did not understand the rest of your question. I would suggest posting a sample workbook with an explanation of what you are after.

Cheers,
Sajan.
 
Hi ,

I am assuming that the formula you have posted will be in columns other than column A and column B. Suppose we assume it is in column C ; the formula :

=IF(A1=MAX($A$1:$A$12),B1,"")

will be in cell C1 , and the above formula will be copied down.

It should populate all cells in column C which have the maximum product , which means if it has the entry 3.2 , it should also have the entry 2.3 , unless ...

The only possibility is if the value in the corresponding cell in column A is not exactly 6 ( 2 x 3 ) , but is a decimal number like 5.99999999

Can you place the cursor in the corresponding cell in column A , press F2 and F9 , and see if it displays 6 ?

Narayan
 
By the way, what should happen if your input value is an integer (like 2). Should the product be zero?
 
Sajan said:
@clumpa,
To get the product, you could try something like:
=MID(J10,1,FIND(".",J10)-1)*MID(J10,FIND(".",J10)+1,255)
where J10 has the number

I did not understand the rest of your question. I would suggest posting a sample workbook with an explanation of what you are after.

Cheers,
Sajan.
Click to expand...

Thanks Sajan, your formula to calculate the product solves my problem whereas
INT(A1)*((A1-INT(A1))*10) PRODUCES THE"CORRECT" VALUE WHICH DOES NOT WORK
 
NARAYANK991 said:
Hi ,

I am assuming that the formula you have posted will be in columns other than column A and column B. Suppose we assume it is in column C ; the formula :

=IF(A1=MAX($A$1:$A$12),B1,"")

will be in cell C1 , and the above formula will be copied down.

It should populate all cells in column C which have the maximum product , which means if it has the entry 3.2 , it should also have the entry 2.3 , unless ...

The only possibility is if the value in the corresponding cell in column A is not exactly 6 ( 2 x 3 ) , but is a decimal number like 5.99999999

Can you place the cursor in the corresponding cell in column A , press F2 and F9 , and see if it displays 6 ?

Narayan
Click to expand...

The first formula I used to calculate the product was =INT(A1)*((A1-INT(A1))*10) which displays the "correct" value but does not work.
I am using a Mac & Microsoft Office and pressing F2/F9 either separately or together does not seem to do anything
Sajan's formula to calculate the product does work, =MID(J10,1,FIND(".",J10)-1)*MID(J10,FIND(".",J10)+1,255)
 
Hi, clumpa!
I'm sure that it isn't the case, but are you using an English Excel version or does your regional configuration settings use a dot as decimal separator and not a comma instead?
Regards!
 
Hi SirJB7,
Since the title of the post, as well as the samples in the initial post all mention decimal point, and not a decimal comma (is there something like that?), I think it is a good assumption that a "point" is involved!

-Sajan.
 
@Sajan
Hi!
That's why I started with "I'm sure that it isn't the case, but...". In Spanish Excel versions, there's a common expression that says: decimal point is comma, but never heard about a decimal comma, yes about comma as decimal separator.
Regards!
 
SirJB7 said:
@Sajan
Hi!
That's why I started with "I'm sure that it isn't the case, but...". In Spanish Excel versions, there's a common expression that says: decimal point is comma, but never heard about a decimal comma, yes about comma as decimal separator.
Regards!
Click to expand...

I use an English version of Excel so a decimal point!
Can anyone tell me what value is in cell C3 in the attached spreadsheet?
 

Attachments

  • seq.xlsx
    22.5 KB · Views: 3
Hi ,

The value is 6 ; in D3 , I put in the formula =C3=6 , and it displayed TRUE.

The problem is not in C3 , but in C7 and C13 , each of which has the value 6.00000000000001.

Since C3 has only 6 , and the maximum value in column C is 6.00000000000001 , you do not get the corresponding value in A3.

Try this formula to get the product :

=INT(A2)*MOD(A2*1000,1000)/100

This seems to work even with numbers having 2 decimal places.

Narayan
 
You must log in or register to reply here.
Back
Top