Help with Formulae please!
 

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.

:confused:


--
Puzzled
------------------------------------------------------------------------
Puzzled's Profile: http://www.excelforum.com/member.php...o&userid=31908
View this thread: http://www.excelforum.com/showthread...hreadid=528449

Help with Formulae please!
 

Try

=(INT(D1/100)+1)*20


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=528449

Help with Formulae please!
 

Not sure about this one, but maybe add this to B2

=IF(AND(SUMPRODUCT(--(LEN($A$2:E2)))=0,F1=100),20,"")

and copy across

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Puzzled" wrote in
message ...

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.

:confused:


--
Puzzled
------------------------------------------------------------------------
Puzzled's Profile:
http://www.excelforum.com/member.php...o&userid=31908
View this thread: http://www.excelforum.com/showthread...hreadid=528449

Help with Formulae please!
 

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,"")

--
HTH

Sandy

with @tiscali.co.uk


"Puzzled" wrote in
message ...

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.

:confused:


--
Puzzled
------------------------------------------------------------------------
Puzzled's Profile:
http://www.excelforum.com/member.php...o&userid=31908
View this thread: http://www.excelforum.com/showthread...hreadid=528449

Help with Formulae please!
 

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),"",(INT(C5/100)-INT(B5/100))*20)

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

Then copy the formula to the right.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=528449

Help with Formulae please!
 

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.


Good point John. An alternative would be:

=IF(ABS(FLOOR(B5,100)-FLOOR(A5,100))=100,20,"")

--
Regards


Sandy

with @tiscali.co.uk


"John James" wrote
in message ...

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),"",(INT(C5/100)-INT(B5/100))*20)

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

Then copy the formula to the right.


--
John James
------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=528449