Picking up a cell as a negative in a formula

**gandyling** · 10-03-2011, 04:21 PM

Hello I was wondering if any one could help. Im trying to get a formula to work. I have two columns one with the figure 45 and other with 1.62%

Im trying to get this sum to work 100-45 x (1.62%) - 100 = (45.89)

I know this is simple but I cant get a formula to work the same way as the sum.The problem im having is with column with the percentage in. I need to pick it up as a minus but cant seem to do this.

**NBVC** · 10-04-2011, 08:13 AM

How exactly are you getting that result with that equation?

**MrShorty** · 10-04-2011, 09:58 AM

As written, the equation is incorrect. It could also be simplified, in that 100-100=0, so the 100's are completely unnecessary. That aside, though, the basic answer to the question if I've understood it correctly is to put a minus "-" sign in front of the reference to that cell. So, assuming +45 is in B2 and +1.62% is in B3, the formula is =100-B2*-B3-100 will multiply 55 (100-45) by -0.0162.

**NBVC** · 10-04-2011, 10:12 AM

Well, I guess I could have said that too... I was just trying to clarify how the result came about to validate that the - was even required in the first place.

**gandyling** · 10-04-2011, 01:07 PM

Originally Posted by MrShorty

As written, the equation is incorrect. It could also be simplified, in that 100-100=0, so the 100's are completely unnecessary. That aside, though, the basic answer to the question if I've understood it correctly is to put a minus "-" sign in front of the reference to that cell. So, assuming +45 is in B2 and +1.62% is in B3, the formula is =100-B2*-B3-100 will multiply 55 (100-45) by -0.0162.

Hi thanks for your help, but its not giving me the answer I need. The calculation 100-45 x (1.62%) - 100 = (45.89) is what is done on a calculator and gives the result. The problem is ive inherited this spreadsheet which has around 500 rows each needing this calculation. They originally just entered each one by one! I cant get my head round it!

**MrShorty** · 10-04-2011, 03:56 PM

The calculation 100-45 x (1.62%) - 100 = (45.89) is what is done on a calculator

That is the question -- how does your calculator get 45.89 out of that (or is it a -45.89? I'm not in finance, so the paranthesis notation for negative numbers is foreign to me)? When I do it (100-45*-0.0162-100), I get 0.729, which tells me I'm not understanding what the formula is really trying to do.

Looking at other combinations of those numbers, I can get -45-(45-100)*-0.0162=-45.89. Any possibility that is the intended formula?

**gandyling** · 10-05-2011, 02:07 AM

Originally Posted by MrShorty

That is the question -- how does your calculator get 45.89 out of that (or is it a -45.89? I'm not in finance, so the paranthesis notation for negative numbers is foreign to me)? When I do it (100-45*-0.0162-100), I get 0.729, which tells me I'm not understanding what the formula is really trying to do.

Looking at other combinations of those numbers, I can get -45-(45-100)*-0.0162=-45.89. Any possibility that is the intended formula?

Yes it is -45.89 to be honest im having trouble understanding it but have been told this is what they do to get this answer! But the answer they then typed into the spreadsheet would be 45.89 !