Posts by FaKest

    Here is the answer for my question.

    And it is possible to use a excel a formula as well.

    Assuming you do not have more than one year of data in the table, given the workbook you downloaded at the other website, for the Amount average for the previous six existing months:

    =AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

    If you might have more than 12 months in the Pivot Table, then you need to check for the year also:

    =AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0})),"Years",YEAR(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

    Correcting myself

    The rule for the semester would be:

    The semester start in the month we are now (like April) and goes back 6 months. Apr, Mar,Feb,Jan,Dec,Nov (it would be the months of the last semester and from this months take the Active months in the pivot) and The active month would be Mar,Feb, Jan 2020 & Dez 2019


    I need a code that will count how many months was active in the past semester.

    I have a pivot and the data changes all the time when the pivot is refreshed, However, I need to calculate the last 6 months average but only using the months that was active in the last semester. e.g
    2020 2019
    Jan May
    Feb Jun

    Mar Jul





    in this case I would take the average for the last 6 months only using 5 months (2020 Jan, Feb, Mar & 2019 Dec, Oct) because it is the months there person was active in the last semester).

    I have this code for the last 6 months in a row

    But I am guessing I need to use IF the last 6 months is = 6 active months in a row then go to SUB Sub Last_6_Months_Average()
    But IF the last 6 months is < 6 active months in a row Then count the number of active months in the last semester and select the total amount of this active months and do the average of the values.
    cells(number of active months, 3).value ="Average"

    Many someone can help me with this code?

    Hi all,

    Not sure how to go about these formulas I'm working on with the data I have been given.

    I have this formula in all my C column and when I don't get a correct date result it instead to be blank will show the date 1/0/1900. The problem is that I am using this column C to create a pivot and the column C is the Pivot's date, however I need to get rid of this year 1900


           any ideas how I could get rid of it or maybe replace for the word Blank?


    The excel user will export the data from an online website to excel (12 months data), so the data will be all the time different. So the formula would need to be dynamic.

    Want to calculate the past 6 months average (However, the calculation need to use the months I have in the data, and sometimes there will be less than 6 or 12 months, or it may be like Jun, Aug, Sep, Dec, -2019 & Feb, Mar, Apr 2020), but I still need to get the average and frequency for it. I am trying different way with this formula

    =IF(MONTH(MAX('12 Months'!A:A)-MIN('12 Months'!A:A))>=6,COUNTIF('12 Months'!A:A,">="&EDATE(MAX('12 Months'!A:A),-6))/6,AVERAGEIF('12 Months'!F:F,">="&MIN('12 Months'!F:F))/MONTH(MAX('12 Months'!F:F)-MIN('12 Months'!F:F))) ''' this well, I simple dont know. :D


    =AVERAGEIF(('12 Months'!A:A),">="&EDATE(MAX('12 Months'!A:A),-12),('12 Months'!F:F)) ''' this calculates as full calendar year and not only 6 months on my data.

    However it doe snot give me the correct Data.

    Ps: I post a similar question on…st-12-and-6-month-average

    You've had a lot of help here, but it seems that you haven't grasped the Forum Rules. especially cross posting.…-error-in-the-same-workbo

    I just reread the rules ( The forum Etiquette* ) , and I understand what do you mean (to be honest with you, first I thought it was only about the same question here in this forum, but now I understood).

    I apologize; it will not happen again.

    Thank you for your time.

    Re: "would it be possible to use something else on the place of the merge cells?"

    Use "CenterAcrossSelection" instead.

    BTW, how are the pictures put onto the Sheet?

    If that is done with code, you can name them as they are put onto the sheet.

    I use a code to put the pictures on the sheet.

    You've had a lot of help here, but it seems that you haven't grasped the Forum Rules. especially cross posting.…-error-in-the-same-workbo

    Yes I did, and I am very thankful for all the help.

    I apologize I will reread the rules.

    It may appear silly, but I did not notice it was the same website or linked in some way.

    I will read the rules once more to make sure it will not happen again.

    I am sorry and thank you for all your help.


    The code is working perfectly fine, and it deletes the pics inside the merge cells. However, I am curious, would it be possible to use something else on the place of the merge cells?

    Thank you.

    The code in Post #9 just replaces this

    Set xRg1 = Range("B75:K136")

    So you should end up with something like this.


    Thank you so much, but unfortunately, is not possible for me to named the pics since I am not sure how many pics it will be. (It will depend on the excel user, sometimes it can be a lot like 4 and sometimes only 1).

    The code worked for me on a mocked up worksheet.

    You could try this,. To use this I would normally have the images named Pic1,Pic2 etc because it will delete all shapes without some checking. Try it on a bakup workbook

    Dim shp As Excel.shape
    For Each shp In ActiveSheet.Shapes