Error Ref

[tiong999]

Hello All,

I'm not a really newbie in excel, but i just realize a simple thing that make me consider myself a newbie by now.

Can anyone let me understand what make my formula return #Ref , when i deleted a row reference? Any expert advise to avoid human effort to deleted a row without #Ref error, especially when i have many sheet depend on main sheet.

My workbook will illustrate within 4 sheet.

For contribution and advice...thanks in advanced.



Regards,
Tiong

 

[bosco_yip]

Try.........

In "Result#Ref sheet" A2 formula copy down :

=IFERROR(1/(1/OFFSET('Deleted#Ref'!A$1,ROWS($1:1)-1,0)),"")

Regards
Bosco

 

[tiong999]

Aaaahhh....i forget to use offset since i didn't realize the power of flexibility.

But. may i asking what the use of
1/(1/ within formula?

Regards,
Tiong

 

[NARAYANK991]

Hi ,

The purpose of using the 1/1/x construct , where x can be any expression , is that there may be situations where the expression x may evaluate to 0.

Now , testing for the condition whether x equals 0 will involve repeating the expression , as in :

=IF(x = 0 , "Some result" , x)

Where x is a lengthy expression , this is clearly cumbersome.

In such cases , usage of the 1/1/x construct reduces the length of the formula , and makes for readability.

Secondly , when we use the formula :

=IF(x = 0 , "Some result" , x)

when x is non-zero , it is evaluated twice , once as a part of the condition check , and once as the output.

To overcome both these inconveniences , we use the IFERROR function with the 1/1/x construct.

1/x is the reciprocal of x ; taking the reciprocal of this by 1/1/x gives us back x.

Thus , where x is non-zero , this does nothing ; but where x is zero , this will result in a DIV/0! error , which is then trapped by the IFERROR function.

Narayan

 

[tiong999]

Well....Thanks for the knowledge u contribute.

Have a good day my friends #Bosco & #Narayan

Regards,
Tiong