Jan 11, 2012

I am trying to do:

Say value in A2 and A3 is 4.5 and 5 respectively

and the value in A1 is and 1 respectively

B2 and B3 have zeros.

Case 1 :

Now I would like to have value A1 in C2:AZ2 till the value in A2 gets exhausted.

Hence C2 = 1, D2 = 1, E2 = 1, F2 = 1, G2 = 0.5, H2 = 0, I2 = 0 ... and so on

Case 2 :

Values in C3:AZ4 should be A1 till A3 gets exhausted. But the constraint is the values in the above range should start from/next cell when we left CASE1

Hence C3 = 0, D3 = 0, E3 = 0, F3 = 0, G3 = 0.5, H3 = 1, I3 = 1, J3 = 1, K3 = 1, L3 = 0.5 ...

I am using the below formula -

For CASE-1 : C2 is

=IF($A$2>0,IF(SUM($B$2:B2)$A$2,$A$1,0),0)

D2 is

=IF($A$2>0,IF(SUM($B$2:C2)$A$2,$A$1,0),0)

and so on...

But this works only if the numbers are whole numbers, I tried a lot but I could not get a formula which works with integers. The above formula will fail if the number is say 4.5 -having decimal part.

For CASE-2 : C3 is =IF($A$3>0,IF(COUNTIF(C$2,1)>0,0,IF(SUM($B$3:B3)$A$3,$A$1,0)),0)

D3 is

=IF($A$3>0,IF(COUNTIF(D$2,1)>0,0,IF(SUM($B$3:C3)$A$3,$A$1,0)),0)

When the numbers in A2 and A3 are decimals and not the whole numbers.

View 9 Replies
View Related