displaying #VALUE in formula output

[arifanmol]

In highlitged cell -in the attached file - (showing #VALUE I want the call address of 'subtotal' to be multiplied with 0.18 using formula
I wish you all kind sirs to modify the expected response

 

[pecoflyer]

I want the call address of 'subtotal' to be multiplied with 0.18

This seems rather obscure..

 

[arifanmol]

I want solution i ll be pleased

 

[arifanmol]

If you have as always modifying solution let me know

 

[joeu2004]

showing #VALUE I want the call address of 'subtotal' to be multiplied with 0.18 using formula

I don't understand the inquiry, because you seem to have done the "right" thing in G10, insofar as that formula correctly calculates the subtotal times 0.18 -- albeit only by coincidence.

The #VALUE error arises because ADDRESS returns a string -- oddly "$C$9".

So, the formula in I10 becomes effectively "$C$9"*0.18. Of course, we cannot multiply that string by 0.18.

OTOH, $C$9 is not the subtotal (G9), anyway. And C9 is empty.

So, why are you calculating C9*0.18, in the first place?

-----

In contrast, the "correct" calculation in G10 is (removing redundant parentheses):
=SUM(F3:OFFSET(INDIRECT(ADDRESS(MATCH("Subtotal",F:F,0),6)),-2,0))*0.18

Breaking that down.... MATCH("Subtotal",F:F,0) returns 9 for the row number, and 6 refers to column 6 (F !).

So, ADDRESS(MATCH("Subtotal",F:F,0),6)) returns "$F$9", and INDIRECT("$F$9") returns the cell reference F9.

Thus, OFFSET(F9,-2,0), which returns a reference to F7.

So, the formula becomes effectively =SUM(F3:F7).

But note: SUM(F3:F7) is the subtotal only by coincidence, because the number of units in column D are all 1.

To see the error, change D3 to 2, for example. The correct subtotal is 11, not 9.

Using your method, the correct subtotal is:
=SUM(G3:OFFSET(INDIRECT(ADDRESS(MATCH("Subtotal",F:F,0),7)),-2,0))*0.18

-----

That said, why are you making things so complicated?!

Why not use the following formulas:
G9: =SUM(G3:G8) ....or =SUM(G3:INDEX(G:G,ROW()-1))
G10: =G9*0.18 ....or better: =ROUND(G9*0.18, 2)
G11: =SUM(G9:G10)

The SUM(G3:INDEX) alternative makes it easy to insert new rows after row 8.

Generally, INDEX is better to use than OFFSET because OFFSET is a "volatile" function. It causes that formula and dependent formulas to be recalculated every time any cell in the workbook is edited, as well as some other times.

ROUND(... , 2) ensures that the result is "dollars-and-cents" (monetary amount with 2 decimal places).

It seems unnecessary for your example. But if F3 were 2.34, for example, G10 would be 1.6812 instead of 1.68.

-----

Aside.... You can also avoid hardcoding 0.18 in two places, F10 and G10.

Instead, enter 18% into F10, and format the cell with Custom 0%" GST". You might also want to format the cell with left alignment.

Then, the formula in G10 becomes =G9*F10 or =ROUND(G9*F10, 2).

 

[arifanmol]

plz check offset formula
right or needs modification

=offset(G9((SUM(F3:(OFFSET(INDIRECT(ADDRESS(MATCH("Subtotal",F:F,0),6)),-2,0))))1,0))