Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 10, 2023

deniztopcu 

You need to define your source somehow in any case. Assuming that's range named "data" and assuming you are on Excel 365 that could be

=LET(
    start, XMATCH($H$19, CHOOSEROWS(data, 1)),
    end, XMATCH(TRUE, CHOOSEROWS(DROP(data, , start), 1) <> "") + start,
    headers, HSTACK({"Name"}, CHOOSEROWS( DROP(data, , COLUMNS(data) - end + 2), 3 ) ),
    VSTACK( headers,
    HSTACK(
        TOCOL(CHOOSECOLS(data, 1), 1),
        DROP(data, 3, COLUMNS(data) - end + 2)
    )
) )

 

deniztopcu's avatar
deniztopcu
Brass Contributor
Jun 10, 2023

SergeiBaklan

I don't have 365, I made the code partially with props. I can't use this code and I realized that I wrote the topic incompletely. outside the main topic
I have one more question.
1) I need to subtract all but Q1 (**/3) for quarterly net worth.

I could do it using if, but I'd stretch this long formula even further.
Instead of entering the formula one by one as in the dark blue region, I want to see the same result as a group as in the yellow region at once.

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 11, 2023

    deniztopcu 

    Sorry, I didn't catch what do you mean exactly "I need to subtract all". In your sample, in blue, we have quarters one by one as

    i.e. in random order. 

     

    More general question - in any case you use  Power Query and PivotTable, why don't do required calculations with them? That shall be more reliable.

     

    Finally, on which Excel version and platform you are?

    • deniztopcu's avatar
      deniztopcu
      Brass Contributor
      Jun 11, 2023

      hi 

      Sergey Baklan

      including several forums here before; Using the subject to do this with a power questioning but getting the solution. It actually works better for me.
      Merges the retelling into the previous message. It would be better for me to restrict the periods from each other with a force query.

      some semantic shifts may be due to translation error.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 11, 2023

        deniztopcu 

        That's not optimal, I kept as the basis your formulae. Result is in green block

        with

        =LET(
    names, A9:A16,
    rate, LET(
        Price, FILTER(
            TCMB_Kur,
            TCMB_Kur[Döviz Cinsi] = P19
        ),
        INDEX(Price, SEQUENCE(1, 6, 2))
    ),
    blockStart, MATCH(Q20, 6:6, 0),
    dataPlus, FILTER(
        8:16,
        (COLUMN(6:6) = 2) +
            (
                (COLUMN(6:6) >= blockStart) *
                    (COLUMN(6:6) <= blockStart + 5 + 1)
            )
    ),
    range, DROP(dataPlus, 1),
    data, DROP(range, , -1),
    dataNext, DROP(range, , 1),
    quarters, DROP(TAKE(dataPlus, 1), , -1),
    (data - dataNext * (RIGHT(quarters) <> "3")) / rate
)

         

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Jun 10, 2023
    but Q1 (**/3) ?

    which sheet is your raw data?
    which range is your expected result?
    • deniztopcu's avatar
      deniztopcu
      Brass Contributor
      Jun 10, 2023

      hi peiyezhu
      raw data and result are on the same page.
      **/3 = YY/3
      My sample file is attached.

      highlighted in blue,
      The numerical results I want. However, instead of entering these data into each cell one by one, I want to obtain them with a single formula as in yellow. The formula in the yellow field returns the searched data as a group. however, these figures are data covering a certain period. I want to get this data with a single formula and quarterly figures.
      end
      If YY/3-6-9-12 = 3, the data in that column should be written exactly,
      if not; the data in the column should be subtracted from the data in the previous column.

      thanks.

       

       

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Jun 11, 2023
        **/3 = YY/3
        what are these asterisks mean?
        YY/3-6-9-12 = 3,
        what does YY mean?
        =LET(a,A9:A16,b,MATCH(Q20,A6:AXX6,0),FILTER(A9:AXX16,(COLUMN(A6:AXX6)=2)+((COLUMN(A6:AXX6)>=b)*(COLUMN(A6:AXX6)<=b+5)))) / LET(Price,FILTER(TCMB_Kur,TCMB_Kur[Döviz Cinsi]=P19),INDEX(Price,SEQUENCE(1,6,2)))
        I have found above formula in range("Q22") (which is in yellow expected result range),have you solved it?
        Sorry,I am lost.

Resources