Welcome Guest
You last visited January 20, 2017, 10:53 am
All times shown are
Eastern Time (GMT-5:00)

# help from the great programmers please

Topic closed. 17 replies. Last post 2 years ago by SergeM.

 Page 1 of 2
florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
 Posted: January 11, 2015, 9:36 am - IP Logged

can someone please give me the formula to take the zero -1 and get the nine instead of a negative one. example 003-111=992 i would like to do my own spreadsheet so i cand make my own modification where needed.

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: January 11, 2015, 9:59 am - IP Logged

for this example assume the target cell  is A1.  =mod(a1,10)   divide number by 10 show only remainder

for this example  you have data in a1and  b1  your result is in c1.  in c1   =mod(a1-b1,10)   divide result by 10 show only remainder

for your specific example. 3 digit numbers    =mod(a1-b1,1000)   divide result by 1000 and show only remainder

roanoke, virginia
United States
Member #145502
August 7, 2013
304 Posts
Offline
 Posted: January 11, 2015, 10:02 am - IP Logged

for this example assume the target cell  is A1.  =mod(a1,10)   divide number by 10 show only remainder

for this example  you have data in a1and  b1  your result is in c1.  in c1   =mod(a1-b1,10)   divide result by 10 show only remainder

for your specific example. 3 digit numbers    =mod(a1-b1,1000)   divide result by 1000 and show only remainder

Yep, What he said

florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
 Posted: January 11, 2015, 10:05 am - IP Logged

thanks for the help much appreciated!!!!

florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
 Posted: January 11, 2015, 10:10 am - IP Logged

i am using openoffice it is giving me error511?

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: January 11, 2015, 10:22 am - IP Logged

I don't use open office but from what I understand many things are very similar.  check the help file for the proper usage for the " mod " command in open office

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: January 11, 2015, 10:26 am - IP Logged

Syntax:

MOD(number; divisor)

For integer arguments this function returnsnumber modulodivisor, that is the remainder whennumber is divided bydivisor.This function is implemented asnumber - divisor * INT( number/divisor) , and this formula gives the result if the arguments are not integer.

Example:

MOD(22; 3)

returns1, the remainder when22 is divided by3.

MOD(11.25; 2.5)

returns 1.25.

they use a semi colon instead of comma

florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
 Posted: January 11, 2015, 10:44 am - IP Logged

Syntax:

MOD(number; divisor)

For integer arguments this function returnsnumber modulodivisor, that is the remainder whennumber is divided bydivisor.This function is implemented asnumber - divisor * INT( number/divisor) , and this formula gives the result if the arguments are not integer.

Example:

MOD(22; 3)

returns1, the remainder when22 is divided by3.

MOD(11.25; 2.5)

returns 1.25.

they use a semi colon instead of comma

thanks for your help i will give that a try . THANKS AGAIN

florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
 Posted: January 11, 2015, 10:55 am - IP Logged

there must be something missing like the if or something now it is simply adding a neg111 to my number. sorry tobe a pain i had this last year but i didnt have my computer backedup then it had a hard drive failure so i lost it all. back then it took me a month to get it with research i was hoping to eliminate all the research.

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: January 11, 2015, 11:18 am - IP Logged

maybe I have misunderstood what you are trying to do    if cell  a1 is 003  and cell b1 is 111   and in cell c1 you have the formula   =mod(a1-b1;1000) then it should show 892.  if this is not what you want,  try stating the problem again with the expected answer.

florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
 Posted: January 11, 2015, 11:19 am - IP Logged

using003-111=992

florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
 Posted: January 11, 2015, 11:22 am - IP Logged

using003-111=992

wanting todo a -111 rundown from previous number but 0 needs to bring a 9

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: January 11, 2015, 11:27 am - IP Logged

to do that you will need to break the digits apart and deal with each separately.     cell a1 0  b1 0  c1 3    cell a2 1  b2 1  c2 1

cell e1    =mod(a1-a2;10)     cell f1   =mod(b1-b2;10)   cell g1   =mod(c1-c2;10)    cell I1  =e1 & f1 & g1

several steps and obviously adjust cell references as needed. the last step would work in Excel  open office ???

florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
 Posted: January 11, 2015, 11:32 am - IP Logged

great that worked ill put more in to check thank you so much now i can get busy with the rest.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: January 11, 2015, 11:34 am - IP Logged

can someone please give me the formula to take the zero -1 and get the nine instead of a negative one. example 003-111=992 i would like to do my own spreadsheet so i cand make my own modification where needed.

You were doing:

10 - 1
10 - 1
3 - 1
=
9
9
2

What ever you do, you get some row where you want the unique numbers from.

Up to you to work it out according to your fancy ideas and your fancy rules.

Instead of 003 you can split to three columns to simplify the task. Excel is made for Cells, it is Excellent-Cell,
or excel with cells?

 Page 1 of 2