software

Within my speardsheet I have a row which contains different values
increasing from 0 to 1,000.

Each time these values increase by 100, I need to be able to
automatically return another number, say 20 in a different row.

Eg

row of data: 0 10 52 87 91 101 135 168 189 225 265
etc.
required result: 20
20

I'd be grateful for any help anyone can give me.

--
Puzzled
------------------------------------------------------------------------
Puzzled's Profile: www.excelforum.com/member.php...oamp;userid=31908
View this thread: www.excelforum.com/showthread...hreadid=528449
Try

=(INT(D1/100) 1)*20--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=528449Not sure about this one, but maybe add this to B2

=IF(AND(SUMPRODUCT(--(LEN($A$2:E2)))=0,F1gt;=100),20,quot;quot;)

and copy across

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Puzzledquot; gt; wrote in
message ...
gt;
gt; Within my speardsheet I have a row which contains different values
gt; increasing from 0 to 1,000.
gt;
gt; Each time these values increase by 100, I need to be able to
gt; automatically return another number, say 20 in a different row.
gt;
gt; Eg
gt;
gt; row of data: 0 10 52 87 91 101 135 168 189 225 265
gt; etc.
gt; required result: 20
gt; 20
gt;
gt; I'd be grateful for any help anyone can give me.
gt;
gt;
gt;
gt;
gt; --
gt; Puzzled
gt; ------------------------------------------------------------------------
gt; Puzzled's Profile:
www.excelforum.com/member.php...oamp;userid=31908
gt; View this thread: www.excelforum.com/showthread...hreadid=528449
gt;
Perhaps I am totally misunderstanding what it is you are asking but with the
data in A5:K5, to get 20's everytime the figures pass another 100 try:

=IF(FLOOR(B5,100)=FLOOR(A5,100) 100,20,quot;quot;)

--
HTH

Sandy
with @tiscali.co.ukquot;Puzzledquot; gt; wrote in
message ...
gt;
gt; Within my speardsheet I have a row which contains different values
gt; increasing from 0 to 1,000.
gt;
gt; Each time these values increase by 100, I need to be able to
gt; automatically return another number, say 20 in a different row.
gt;
gt; Eg
gt;
gt; row of data: 0 10 52 87 91 101 135 168 189 225 265
gt; etc.
gt; required result: 20
gt; 20
gt;
gt; I'd be grateful for any help anyone can give me.
gt;
gt;
gt;
gt;
gt; --
gt; Puzzled
gt; ------------------------------------------------------------------------
gt; Puzzled's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31908
gt; View this thread: www.excelforum.com/showthread...hreadid=528449
gt;

Sorry, Puzzled. Totally misread your question (through the required
output spacing gaps automatically being trimmed when you posted).

Sandy's got it. Perhaps you may also need to adjust that formula to
allow for a 100 series being jumped (e.g going directly from 385 to
507, missing the 400 series), being re-entered (e.g. through a decrease
from 402 to 399) or the first entry being in the 100's.

On these assumptions, and with your label in A5, B5 being empty and
first figure in C5, my formula in column C should have read:
=IF(INT(c5/100)=INT(B5/100),quot;quot;,(INT(C5/100)-INT(B5/100))*20)

Or you can adjust Sandy's Floor formula similarly.
=IF(FLOOR(C5,100)=FLOOR(B5,100),quot;quot;,(FLOOR(C5,100)-FLOOR(B5,100))*0.2)

Then copy the formula to the right.--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=528449gt; Sandy's got it. Perhaps you may also need to adjust that formula to
gt; allow for a 100 series being jumped (e.g going directly from 385 to
gt; 507, missing the 400 series), being re-entered (e.g. through a decrease
gt; from 402 to 399) or the first entry being in the 100's.Good point John. An alternative would be:

=IF(ABS(FLOOR(B5,100)-FLOOR(A5,100))gt;=100,20,quot;quot;)

--
RegardsSandy
with @tiscali.co.ukquot;John Jamesquot; gt; wrote
in message ...
gt;
gt; Sorry, Puzzled. Totally misread your question (through the required
gt; output spacing gaps automatically being trimmed when you posted).
gt;
gt; Sandy's got it. Perhaps you may also need to adjust that formula to
gt; allow for a 100 series being jumped (e.g going directly from 385 to
gt; 507, missing the 400 series), being re-entered (e.g. through a decrease
gt; from 402 to 399) or the first entry being in the 100's.
gt;
gt; On these assumptions, and with your label in A5, B5 being empty and
gt; first figure in C5, my formula in column C should have read:
gt; =IF(INT(c5/100)=INT(B5/100),quot;quot;,(INT(C5/100)-INT(B5/100))*20)
gt;
gt; Or you can adjust Sandy's Floor formula similarly.
gt; =IF(FLOOR(C5,100)=FLOOR(B5,100),quot;quot;,(FLOOR(C5,100)-FLOOR(B5,100))*0.2)
gt;
gt; Then copy the formula to the right.
gt;
gt;
gt; --
gt; John James
gt; ------------------------------------------------------------------------
gt; John James's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32690
gt; View this thread: www.excelforum.com/showthread...hreadid=528449
gt;