Posts by FaKest

Here is the answer for my question.

Function AverageSemester()

    Dim Ws As Worksheet
    Dim Semester1 As Variant, Semester2 As Variant
    Dim mySemester As Variant
    Dim Target As Range, rngLast As Range
    Dim s As String
    Dim r As Integer, i As Integer

Application.Volatile
    Semester1 = Array("May", "Jun", "Jul", "Aug", "Sep", "Oct")
    Semester2 = Array("Nov", "Dec", "Jan", "Feb", "Mar", "Apr")
Set Ws = Sheets(1)
With Ws
        r = .Range("c" & Rows.Count).End(xlUp).Row - 1
Set rngLast = .Range("b" & r).Offset(, 1)
        s = .Range("b" & r)
If isSemes(s, Semester1) Then
            mySemester = Semester1
        Else
            mySemester = Semester2
        End If
        For i = r To r - 12 Step -1
            s = .Range("b" & i)
            If isSemes(s, mySemester) Then
                Set Target = .Range("b" & i).Offset(, 1)
            Else
                Exit For
            End If
        Next i
        AverageSemester = WorksheetFunction.Average(.Range(Target, rngLast))
    End With
End Function

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}))),""))

Hello, Mr Roy,

Yes I did, however I was not sure if I should send a email directly to Super Moderator or I could reported my post, So I decided to report it.

Quote from royUK

Can you attach an example workbook.

Here is an example workbook.

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

Ps: I posted a similar question here https://stackoverflow.com/ques…ng-function-lastquarter-2 as well

Hello,

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

Aug

Sep

Oct

Dec

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

Sub Last_6_Months_Average()


Dim lastRow As Long, length As Long
With Sheet1
lastRow = .Cells(Rows.count, 3).End(xlUp).row
length = Range(.Cells(lastRow - 1, 3), .Cells(lastRow - 1, 3).End(xlUp)).count
If length > 6 Then length = 6
.Range("D5").Formula = "=AVERAGE(C" & lastRow - length & ":C" & lastRow - 1 & ")"
End With


End Sub

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()
else
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?

Quote from AliGW

This?

=IF(COUNTIF($B$1:B2,B2)=1,A2,"")

Yes, Thank you so much

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

=IF(COUNTIF($B$1:B2,B2)=1,A2,0)


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

Here is the Answer:

A:A- Date column, B:B - Amount Column

=IF(MONTHS(MAX(A:A)-MIN(A:A))>=6;SUMIF(A:A;">="&EDATE(MAX(A:A);-6);B:B)/6;SUMIF(A:A;">="&EDATE(MAX(A:A);-6);B:B)/MONTHS(MAX(A:A)-MIN(A:A)))

Hello,

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.

and

=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 https://stackoverflow.com/ques…st-12-and-6-month-average

Quote from royUK

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

https://stackoverflow.com/ques…-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.

Quote from jolivanes

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.

Sub L2_Bene1()


On Error GoTo ErrorMessage


Alert1.Range("B145").Select


Application.CommandBars.ExecuteMso "ScreenClipping"


If Not Application.CalculationState = xlDone Then


   DoEvents


End If


Call Fit_Pic2_L2  'this code is to fix the pic inside the cell.


Exit Sub

Quote from royUK

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

https://stackoverflow.com/ques…-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.

Jolivanes,

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.

Quote from jolivanes

The code in Post #9 just replaces this

Code

Set xRg1 = Range("B75:K136")

So you should end up with something like this.

Code

Sub ClearPictures_B1_L2()
Dim xPicRg1 As Range
Dim xPic1 As Picture
Dim xRg1 As Range
Dim topLeft As Range, bottomRight As Range


If Range("B75").MergeCells = True Then
Set topLeft = Range("B75").MergeArea
Else
Set topLeft = Range("B75")
End If


If Range("K136").MergeCells = True Then
Set bottomRight = Range("K136").MergeArea
Else
Set bottomRight = Range("K136")
End If


Set xRg1 = Range(topLeft, bottomRight)
For Each xPic1 In ActiveSheet.Pictures
Set xPicRg1 = Range(xPic1.TopLeftCell.Address & ":" & xPic1.BottomRightCell.Address)
If Not Intersect(xRg1, xPicRg1) Is Nothing Then xPic1.Delete
Next


End Sub

Display More

Roy,

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).

Quote from royUK

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

Code

Dim shp As Excel.shape

For Each shp In ActiveSheet.Shapes   
     shp.Delete
Next

Hello Jolivanes,

The code runs fine with no error. However it does not delete the picture.
The code runs, but nothings happens.

Roy,

I still get the error Run-Time error '13': Type mismatch

hi Roy,

Thank you for taking the time to answer me.

it is 3 sheets and all 3 sheets have merged cells to put this pictures and normally it will have 1 or 2 pics in few of them. All of them have the button clear all to be easy to delete everything inside (it will only be the snip from the internet, the same kind of pictures) at once.

Hello Jolivanes,

Thank you for answering. I dont know how many snips the excel user will put in the range.
Would it be possibly to it if I am not sure if it will be only one or 4 pictures? is possible to use another code to delete everything that will be inside the range?
Or maybe add something to my code so it would work all the time?

hi, Logit.

Thank you for answering; However, it is not a option, the range with merged cell is needed specified for paste the snipping image from the internet.

I am trying to understand why it works sometimes in 1 sheet but not in another and what can I do for it to work in all the sheet all the time the button clear all is clicked.