convert percentage in excel

[jassy6939]

Dear All,

Please help me if anyone can.

In my excel column no O17 amount is 16937 when I convert in 1000 format it just comes 17 and column no N17 amount is 71800 when it convert to 1000 format it comes 71.8. In column O18 I just enter equation
=(O17*4)/N17-1 the figure which I got is 94320% but actual it must be -5%. I don't know what reason amount showing wrongly. Please help me if someone knows the reason. I attach my excel sheet.

 

[Keebellah]

Your file contains links to the original file, cannot help with invalid links

 

[Chawbacon]

Hello Jassy,

Based upon your desired outcome of -.05%. It appears that you are missing a pair of parenthesis.

Try the following: =((O17*4)/N17)-1

Good Luck

 

[jassy6939]

Dear All,

Please help me if anyone can.

In my excel column no O17 amount is 16937 when I convert in 1000 format it just comes 17 and column no N17 amount is 71800 when it convert to 1000 format it comes 71.8. In column O18 I just enter equation
=(O17*4)/N17-1 the figure which I got is 94320% but actual it must be -5%. I don't know what reason amount showing wrongly. Please help me if someone knows the reason. I attach my excel sheet.

Dear All,

Please help me if anyone can.

In my excel column no O17 amount is 16937 when I convert in 1000 format it just comes 17 and column no N17 amount is 71800 when it convert to 1000 format it comes 71.8. In column O18 I just enter equation
=(O17*4)/N17-1 the figure which I got is 94320% but actual it must be -5%. I don't know what reason amount showing wrongly. Please help me if someone knows the reason. I attach my excel sheet.

Hello Jassy,

Based upon your desired outcome of -.05%. It appears that you are missing a pair of parenthesis.

Try the following: =((O17*4)/N17)-1

Good Luck

Hi Jack,

Thanks for your reply. When I tried the same it's showing same result

 

[Chawbacon]

Hello again Jassy,

The formula I provided forces Excel to perform the desired math function without possibility of ambiguity. Therefore the root cause will be probably be associated with your linked data. Excel calculates on the stored data and not the displayed data. An additional possibility would be a formatting issue with the current sheet or with the linked data. Custom formatting within Excel can cause some strange outputs, especially when rounding is involved.

As Hans stated above, without data referring to invalid links, a resolution will be primarily guess work.

The best of luck,

 

[jassy6939]

Hello again Jassy,

The formula I provided forces Excel to perform the desired math function without possibility of ambiguity. Therefore the root cause will be probably be associated with your linked data. Excel calculates on the stored data and not the displayed data. An additional possibility would be a formatting issue with the current sheet or with the linked data. Custom formatting within Excel can cause some strange outputs, especially when rounding is involved.

As Hans stated above, without data referring to invalid links, a resolution will be primarily guess work.

The best of luck,

Dear Jack,

Noted, Thank you....

 

[jassy6939]

Dear Jack,

In my excel column no O17 amount is 16937 when I convert in 1000 format it just comes 17 and column no N17 amount is 71800 when it convert to 1000 format it comes 71.8. In column O18 I just enter equation
=(O17*4)/N17-1 the figure which I got is 94320%.

Now I understand how excel consider my equation. Excel consider in my equation =(O17*4)/N17-1 instead of taking (17*4)/71.8-1 excel consider (16937*4)/71.8-1=94320%. Do you have any solution for this??

 

[Chawbacon]

Dear Jack,

In my excel column no O17 amount is 16937 when I convert in 1000 format it just comes 17 and column no N17 amount is 71800 when it convert to 1000 format it comes 71.8. In column O18 I just enter equation
=(O17*4)/N17-1 the figure which I got is 94320%.

Now I understand how excel consider my equation. Excel consider in my equation =(O17*4)/N17-1 instead of taking (17*4)/71.8-1 excel consider (16937*4)/71.8-1=94320%. Do you have any solution for this??

Hi Jassy,

So the root cause here seems to be that the O17 cell is not converting to 1000 format.
Personally, I would adjust the formula to force the value conversion to 1000 format (similar to database programming... run computations on the final form value and not within the database).

(((16937*.001)*4)/71.8)-1

Make sure you test this with other data sets to ensure data change compatibility!

I hope this helps,