Is this Normal with OFFSET() function

[Somendra Misra]

Hello Experts,

Kindly see the attached image. I had use offset function in SUM function. The formula is returning the right value. But when I checked the formula in Formula Evaluater I am getting #Value error, but still it is returning right answer.

The function can be seen in formula bar. The purpose of formula is to add I5,L5,O5 and so on.

Can somebody explain why it is like that?

Regards,

 

[Luke M]

That is very odd! I have never see that before.
If it does start causing problems, could replace that formula with this array formula:
=SUM(IF(MOD(COLUMN($I5:$BB5),3)=0,$I5:$BB5))

 

[Somendra Misra]

Hi Luke,

Yes ODD for me too , the function is returning correct result. Even if I select Number 1 in SUM function and press F9 I get all values. But in Function Evaluation it is showing like that.

I like your formula, as it is not VOLATILE .

Regards,

 

[Debraj]

Hi @Somendra Misra !

* Two Clues..
One.. from your picture..



and Second from your post..

Hi Luke,
I like your formula, as it is not VOLATILE .

No .. I will not give you any HINT..
By the way. is the below is feasible in your sheet..
=SUMIF($I$4:$X$4,"Slot Alloted",$I5:$X5)

 

[NARAYANK991]

Hi Misra ,

I use Excel 2007 , and I do not get a valid value. I assume that the cell F5 refers to the first row of data ; since you are using the OFFSET function with the columnoffset parameter changing , what you have done is the same as the following :

=OFFSET($I5,,{0,3,6,9,12,15,18,21,24,27,30,33,36,39,42,45},1,1)

Try this first and see what happens.

Next , try this :

=SUM(OFFSET($I5,,{0,3,6,9,12,15,18,21,24,27,30,33,36,39,42,45},1,1))

Lastly , try this :

=SUM(N(OFFSET($I5,,{0,3,6,9,12,15,18,21,24,27,30,33,36,39,42,45},1,1)))

The use of the TRANSPOSE function may be right , I do not know , but in my opinion , the TRANSPOSE should be used only when a row vector is being converted into a column vector or vice-versa.

Narayan

 

[SirJB7]

Hi, Somendra Misra!
What about uploading a sample file?
Regards!

 

[Somendra Misra]

FIrst of all A very bigh thanks to all the experts.

@Debraj
Daroon dhorecho dada! Could have used your formula, I was just practicising.

@NARAYANK991 Sir, Yes you are right. I think Its the OFFSET function only as suggested by DEBRAJ because when I evaluate your last function, after the step of OFFSET function same things happens but after the excution of N() everthing is normal.

@SirJB7 ... As I cannot post the original file, I will make a dummy one and will surely post it.

Thanks ones again.

Regards,

 

[Debraj]

@Somendra Misra

By the way "Aamio ei ta prothom baar dekhlam.. "
(I have also seen it for the first time..) the comment in the EVALUATION section..