how to spread a value to diffrerent cell. if cell A1 is above 5000, i want to allocate first 1000 in B1, next 1000 to 2000 in B2 next 2000 to 5000 in B3 like that.

I'm not sure your question makes sense. Do you really want "next 2000 to 5000 in B3"? What if A1 contains 6034? B1 would show 1000 B2 would show 1000 B3 would show what? If what you really want is 1000 in each cell until you run out of 1000's, with the remainder in the last cell, put this in B1 and AutoFill as far down as you want: =IF($A$1>5000,IF($A$1-((ROW()-1)*1000)>0,IF($A$1-((ROW()-1)*1000)<1000,$A$1-(ROW()-1)*1000,1000),""),"") In my example above (6034) you'd get: B1 1000 B2 1000 B3 1000 B4 1000 B5 1000 B6 1000 B7 34 B8 Empty B9 Empty etc.