# Posts by FaKest

• ## How to calculate how many months is active in a semester?

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:

Code
``=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}))),""))``
• ## How to calculate how many months is active in a semester?

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.

• ## How to calculate how many months is active in a semester?

Can you attach an example workbook.

Here is an example workbook.

• ## How to calculate how many months is active in a semester?

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

• ## How to calculate how many months is active in a semester?

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

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?

• ## How to get rid of 1/0/1900 in IF formula?

This?

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

Yes, Thank you so much

• ## How to get rid of 1/0/1900 in IF formula?

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?

• ## Dynamic way to calculate the last 6 months average.

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

• ## Dynamic way to calculate the last 6 months average.

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

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

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

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.

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

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

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.

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

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

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.

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

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.

The code in Post #9 just replaces this

Code
``Set xRg1 = Range("B75:K136")``

So you should end up with something like this.

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

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

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``````
• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

Hello Jolivanes,

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

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

Roy,

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

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

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.

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

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?

• ## Why would a code sometimes run fine and sometimes get a error in the same workbook?

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.