• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Same directing text in a cell everytime

sorry G10 and G11
but see
If there is subtotal anywhere then to its right there should be this formula =SUM(F3:(OFFSET(INDIRECT(ADDRESS(MATCH("Subtotal",F:F,0),6)),-2,0)))
just under it it should have this formula =(SUM(F3:(OFFSET(INDIRECT(ADDRESS(MATCH("Subtotal",F:F,0),6)),-2,0))))*0.18
Moderator note: >> You could use I C O D E - tag to able readable formulas <<
 

Attachments

Last edited by a moderator:
For the effort spent creating a yellow fill it would have been possible to create a defined name and add meaning to the problem.
For example, using 365,
Code: 
= LET(
    taxRate,  18%,
    quantity, CHOOSECOLS(table1, 4),
    unitCost, CHOOSECOLS(table1, 6),
    price,    quantity * unitCost,
    subtotal, SUM(price),
    GST,      taxRate * subtotal,
    VSTACK(subtotal, GST, subtotal+GST)
  )
 
Firstly, the 'code' I showed above is not a macro. It is a regular (but not yet that common) Excel 365 worksheet formula.

LET accepts parameters in twos; the first of each pair is a variable name and the second a formula that is evaluated. Thus the first line sets the tax rate to 18%. The line break in the formula is achieved by typing Alt/Enter. CHOOSECOLS is a new Excel function that allows the user to pick out columns from a 2D array. The next 3 lines are the core calculation which involves no more than simple arithmetic. The final VSTACK just takes the subtotal, the GST and the sum of the two and places them as elements of a 3-cell array which is returned as a dynamic array spilling from the cell with the formula.

Once again, the formula requires Excel 365 to work.
 

Attachments

You must log in or register to reply here.
Back
Top