Help with Formulae please!

P

Puzzled

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

Each time these values increase by 100, I need to be able t
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
 
B

Bob Phillips

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)
 
S

Sandy Mann

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
[email protected]
[email protected] with @tiscali.co.uk
 
J

John James

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.
 
S

Sandy Mann

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.
Click to expand...


Good point John. An alternative would be:

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

--
Regards


Sandy
[email protected]
[email protected] with @tiscali.co.uk
 
Top