Need some help for 2 formulas for a Pivot Table re: Calculated Field

Long story short I am working on an excel file and I have an assignment so I converted over the data to various pivot tables in order to answer some of the questions. The remaining questions are as follows:

5. Average Length of Stay of these patients by CTAS (Registration to Time Pt Left ED)
6. Percentage of CTAS 1-3 treated by Nurse Practitioner within 7 hours (Registration to Disposition (DISP))
7. Percentage of CTAS 4-5 treated Nurse Practitioner within 4 hours (Registration to Disposition (DISP))

With respect to question 5, I was able to create a pivot table but the answers I received (times formated in H:mm btw) in my pivot table (when I created a Calculated field) differ then when I manually averaged the numbers using the AVERAGE formula. Anyone know why they differ?

With respect to questions 6 & 7, they are similar so I will discuss 6. In in my pivot table (when I created a Calculated field), I can't create a formula using the insert calculate field that will give me numbers similar to what I found when doing a manual calculation. I am looking for a formula that will provide me a count for those data ranges that are less than or equal to 7:00 (h:mm). I was able to do it manually but I can't do it via the Pivot Tables route.

Can anybody please help??

Ok - uploaded and thank you. I was going to put the specific formula's that I tried to use originally in this thread but when I did, I had an SQL error. Anyone know why?

The tabs I am referring to that I need help with are the last 2 (Avg Length Stay by CTAS & %'age of CTAS 1-3 NPR's (7)). Regarding the formula's in the last tab, those were not the specific formula's I used rather I used them as templates being that I am relatively new to adding formula's into pivot tables

Okay AliGW - thank you very much. I entered spaces but still no luck

The blocked message I received when trying to post the formula:

Block ID: SQLi17
Block reason: SQL injection was detected and blocked.

With respect to question 5, I was able to create a pivot table but the answers I received (times formated in H:mm btw) in my pivot table (when I created a Calculated field) differ then when I manually averaged the numbers using the AVERAGE formula. The formula I inputed into the Formula field for the Insert Calculate Field was as follows "=AVERAGE('Dif between TIME PT LEFT and REG TIME' )" where 'TIME PT LEFT and REG TIME' in the input field I created and calculated the time difference between the 2 times (in 2 different columns) using =MOD(X2-I2,1) for example because some of the times overlap between 2 dates making a resulting column which is 'TIME PT LEFT and REG TIME' . Manually I figured out the averages by using the following formula (example) =AVERAGE('ED Data'!AH2:AH7). Anyone know why they differ?

With respect to questions 6 & 7, they are similar so I will discuss 6. In in my pivot table (when I created a Calculated field), I can't create a formula using the insert calculate field that will give me numbers similar to what I found when doing a manual calculation. I am looking for a formula that will provide me a count for those data ranges that are less than or equal to 7:00 (h:mm).

I was able to do it manually but I can't do it via the Pivot Tables route. Also for this I created an additional column that gives the difference between 'Registration to Disposition' times and was trying to insert into the Formula field in the Pivot Tables create a Calculate field.

The formulas I tried to use was to give a count to all those time ranges that were less then or equal to the 7:00 and to give a count (I tried using CountIF, Count and Frequency but to no avail).

The last CountIF formula I tried to use was similar to = COUNT(ROUND('Diff between Reg and Disp Time',1) < =TIMEVALUE(7)).

Wrt my last 5 posts, I had to break them up in order to post so I wouldn't get the block error message. The first one refers to question 5 and posts 2-5 refer to questions 6&7.

Originally Posted by KatMat232

Long story short I am working on an excel file and I have an assignment so I converted over the data to various pivot tables in order to answer some of the questions. The remaining questions are as follows:

5. Average Length of Stay of these patients by CTAS (Registration to Time Pt Left ED)
6. Percentage of CTAS 1-3 treated by Nurse Practitioner within 7 hours (Registration to Disposition (DISP))
7. Percentage of CTAS 4-5 treated Nurse Practitioner within 4 hours (Registration to Disposition (DISP))

With respect to question 5, I was able to create a pivot table but the answers I received (times formated in H:mm btw) in my pivot table (when I created a Calculated field) differ then when I manually averaged the numbers using the AVERAGE formula. Anyone know why they differ?

With respect to questions 6 & 7, they are similar so I will discuss 6. In in my pivot table (when I created a Calculated field), I can't create a formula using the insert calculate field that will give me numbers similar to what I found when doing a manual calculation. I am looking for a formula that will provide me a count for those data ranges that are less than or equal to 7:00 (h:mm). I was able to do it manually but I can't do it via the Pivot Tables route.

Can anybody please help??

Hi:
I was wondering if you still have your caiculation. Thanks