I need to update the formula

[akinkaraman]

In Page 1 Index formula is working.

The indexes of line 1-200 and 201-500 are same. So I combined them as 1-500 in Page 2

I need to write the formula to find the indexes in Page 2 similar as Page 1.

Can you help me?

 

[srinidhi]

Hope this is what you are looking for.....

 

[akinkaraman]

Hi. The formula is not working right.

For example in Zone A 1000 shows Index -10 but the Index should be -7 according to table.

 

[NARAYANK991]

Hi ,

Is this OK ?

=IF(C4=0,"",IF(C4<7501,INDIRECT(ADDRESS(8+IF(C4<=500,1,IF(INT(ROUNDDOWN(C4/500,0)=(C4/500)),(C4/500),ROUNDDOWN(C4/500,0)+1)),COLUMN(C4),1,1)),"N/A"))

I have no idea why the constant 7501 has been used ; you need to decide whether this is to be used or some other figure should be used instead.

Narayan

 

[akinkaraman]

Yes Mr. Narayan, it is working good.

Thank you very much..

(Every Zone has its limits. Such as Zone A limit is 7500 but Zone B limit is 10000)