Welcome Guest
You last visited April 30, 2017, 6:43 am
All times shown are
Eastern Time (GMT-5:00)

# Need an Excel formula

Topic closed. 7 replies. Last post 2 years ago by Murgatroyd.

 Page 1 of 1
Member #19992
August 9, 2005
2872 Posts
Offline
 Posted: March 2, 2015, 10:21 pm - IP Logged

I want to "minus" -111 from a number but I want to show a "positive" number that would be a 3 digit number.

Can anyone do this in Excel

for example...

 439 328 217 106 -005 -116 -227 -338

as you can see 106 minus -111 should be 995 not -005

it then screws up everything after... can someone please formulate this for excel for me please.

thank you

We are all Lucky... just some of us don't realize it!

United States
Member #41846
June 23, 2006
462 Posts
Offline
 Posted: March 3, 2015, 7:20 pm - IP Logged

I am not very good with if statements in excel so I will do this in two steps

assumption  your result is in row C  as in a1=106  b1=111  c1=a1-b1

step 1: you first result ( a negative  # in this case is in cell c1)

step 2  in D1 enter  =IF(C1<0,(1000+C1),C1)  now you will have the positive number you are seeking.  hide col C or ignore it

change cell references as needed

copy down as needed

Krypton
United States
Member #140102
March 11, 2013
907 Posts
Offline
 Posted: March 4, 2015, 6:26 pm - IP Logged

I am not very good with if statements in excel so I will do this in two steps

assumption  your result is in row C  as in a1=106  b1=111  c1=a1-b1

step 1: you first result ( a negative  # in this case is in cell c1)

step 2  in D1 enter  =IF(C1<0,(1000+C1),C1)  now you will have the positive number you are seeking.  hide col C or ignore it

change cell references as needed

copy down as needed

Did this answer your excel needs?  If not, let me know and I'll write the formula perhaps differently but looks like Phil did great

Stay In The Vortex, you'll be happy you did ..... Random? Seriously? You want me to believe that?

Pennsylvania
United States
Member #2218
September 1, 2003
5464 Posts
Offline
 Posted: March 10, 2015, 9:28 am - IP Logged
Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: March 17, 2015, 6:20 pm - IP Logged

Remarkable long piece of coding. You have Excel tradition.

mid-Ohio
United States
Member #9
March 24, 2001
20022 Posts
Offline
 Posted: March 18, 2015, 10:37 am - IP Logged

I want to "minus" -111 from a number but I want to show a "positive" number that would be a 3 digit number.

Can anyone do this in Excel

for example...

 439 328 217 106 -005 -116 -227 -338

as you can see 106 minus -111 should be 995 not -005

it then screws up everything after... can someone please formulate this for excel for me please.

thank you

If you can do it with paper and pencil, why do you need Excel?

* you don't need to buy more tickets, just buy a winning ticket *

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: March 18, 2015, 12:43 pm - IP Logged

He eventually has seen my reaction on a thread of Pick 3 on Lotterypost.

I solve the problem with one single custom function.

The problem can be solved with Excel functions too, you don't need to program. A Google spreadsheet probably does it too, just like other Excel like programs.

I give a lot of credit to pen and pencil, as I never forget that couple.

Arizona
United States
Member #165073
March 24, 2015
220 Posts
Offline
 Posted: March 26, 2015, 8:46 pm - IP Logged

Try MOD(a1-111, 1000)

(If you want to subtract from a number in a cell other than a1, adjust accordingly.)

 Page 1 of 1