Brain twister

  • Thread starter Thread starter Greg Incongito
  • Start date Start date
G

Greg Incongito

Hi group,
here is my setup

I get five input rows in columns A and B. In column B,
there is a total layer, where A may be equal to total in B
or may have increments, such that if A1 has 500, A2 –
1000, A3 – 500, B1 through B2 will all contain 2000 (sum
of A1-A3). The problem is that in column C I need to show
the layer number based only on the relationship between A
and B.

Here is what I need in C for the following examples:

A B C
100 100 1
300 300 2
500 2000 3
1000 2000 3
500 2000 3


A B C
100 500 1
100 500 1
100 500 1
100 500 1
100 500 1

A B C
500 1000 1
500 1000 1
100 100 2
250 500 3
250 500 3

Hope I explained it clearly. I can use nested Ifs, of
course, but I am sure there should be a more elegant
solution.
Appreciate your help in tackling this problem.

Regards,
Greg
 
I would wait for an expert answer but in the meanwhile,
try looking up Pivot tables, Vlookup and Hlookup in Excel
help and see if that gets you close to what you are
looking for.

Sorry I couldn't be of more help!
 
Well, I would afraid that expert answer would never come
now since you replied, but thanks anyway for trying!
Best wishes,
Greg
 
Hi Greg
if your data starts in row 1 try the following:
- In C1 enter 1 (as this has to be the first layer)
- in C2 enter the following formula:
=IF(SUM(OFFSET($A$1,MATCH(C1,$C$1:C1,0)-1,0,ROW()-MATCH(C1,$C$1:C1,0)))
=B1,C1+1,C1)
copy this down

Frank
 
Works like a charm! Thank you very much.
Greg
-----Original Message-----
Hi Greg
if your data starts in row 1 try the following:
- In C1 enter 1 (as this has to be the first layer)
- in C2 enter the following formula:
=IF(SUM(OFFSET($A$1,MATCH(C1,$C$1:C1,0)-1,0,ROW()-MATCH (C1,$C$1:C1,0)))
=B1,C1+1,C1)
copy this down

Frank






.
Click to expand...
 
Hi again,
I am not sure if this is already considered a stale post.

I am wondering if it is possible to make the below formula
more robust, since I foget about scenarios when any one or
more rows could be blank.

Thank you very much,
Greg
 
Hi Greg
can you give an example for that scenario and also the exspected result
in column C

Frank
 
Back
Top