excel spreadsheet formula

king0242

New Member
Joined
Jan 2, 2009
Messages
29
every 1000 in A1, then B1 will increase by 1

e.g.
if A1 is 990, then B1 is 1
if A1 is 6120, then B1 is 7

how to write formula for B1
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
I'd probably set up a table like below and do it that way

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">6120</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1000</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1001</TD><TD style="TEXT-ALIGN: right">2000</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">2001</TD><TD style="TEXT-ALIGN: right">3000</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3001</TD><TD style="TEXT-ALIGN: right">4000</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">4001</TD><TD style="TEXT-ALIGN: right">5000</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">5001</TD><TD style="TEXT-ALIGN: right">6000</TD><TD style="TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">6001</TD><TD style="TEXT-ALIGN: right">7000</TD><TD style="TEXT-ALIGN: center">7</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">7001</TD><TD style="TEXT-ALIGN: right">8000</TD><TD style="TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">8001</TD><TD style="TEXT-ALIGN: right">9000</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">9001</TD><TD style="TEXT-ALIGN: right">10000</TD><TD style="TEXT-ALIGN: center">10</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=LOOKUP(A1,C1:E10,E1:E10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,876
Office Version
  1. 365
Platform
  1. Windows
every 1000 in A1, then B1 will increase by 1

e.g.
if A1 is 990, then B1 is 1
if A1 is 6120, then B1 is 7

how to write formula for B1
Not quite sure what you want if A1 is exactly equal to some thousands (like 5000), but one of these should help:

=INT(A1/1000)+1

or

=CEILING(A1,1000)/1000
 
Upvote 0

Forum statistics

Threads
1,195,961
Messages
6,012,582
Members
441,713
Latest member
Dave353

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top