Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Help with figure this issue

Latest post Wed, Aug 13 2014 10:13 AM by PeterG. 15 replies.
  • Mon, Aug 4 2014 4:06 PM

    • Jerry
    • Top 150 Contributor
    • Joined on Mon, Aug 4 2014
    • Atlanta
    • Posts 8
    • Points 168

    Help with figure this issue

    (A) has a list of charges received and (B) is the category on each charges and (C) is the insurance that charged (A). now I am trying to get the average of Charges (A) per categories (b) for all the insurances (C). Some of the charges are not on the same rows which makes it even harder for me. Someone could help me with this issue. Thanks.

    Filed under: ,
    • Post Points: 21
  • Tue, Aug 5 2014 4:59 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 224
    • Points 3,368

    Jerry

    I'm not quite sure what you want to calculate, but a general approach might be to fill in all the empty cells in column A with a formula that returns the 'Svc Procedure Code' from the previous row. A quick way to do this is to use the Excel filter to show all the empty cells in column A, add an appropriate formula into the first cell and then drag it down all the remaining cells. You can then use a pivot table to analyze the data.

    I truncated your data and then used the 'Options' tab to attach a workbook (Example_2.xlsx) to this reply so that you can see the type of approach that I am suggesting. I've shaded the formulas with yellow background so that you can see them. The pivot table is on Sheet2.

    Peter

    • Post Points: 21
  • Tue, Aug 5 2014 8:36 AM In reply to

    • Jerry
    • Top 150 Contributor
    • Joined on Mon, Aug 4 2014
    • Atlanta
    • Posts 8
    • Points 168

    Re: Help with figure this issue

    Peter,

    Thank you so much for your input and your time. I thought about eliminating the blanks in column A but my issue is that Column C is contains charges that are part of a category. For instance, in Column A "99215" is a category that has charges from " self pay, UHC1, UHC1" now I am not good with excel but if you eliminate the blanks in column A isnt it going to affect the charges that are in that same row?

    • Post Points: 21
  • Tue, Aug 5 2014 9:16 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 224
    • Points 3,368

    Re: Help with figure this issue

    Jerry

    It looks like I've misunderstood what you want to calculate, and having reread your original post, I am still unclear about what you are wanting to do.

    Could you attach a workbook with a small data set and show exactly the results table you are hoping to calculate?

    Peter

    • Post Points: 21
  • Tue, Aug 5 2014 10:03 AM In reply to

    • Jerry
    • Top 150 Contributor
    • Joined on Mon, Aug 4 2014
    • Atlanta
    • Posts 8
    • Points 168

    Re: Help with figure this issue

    I hope that this one will help more with understanding my situation. I am looking to get the average for all 99213 in the spreadsheet and so on. My obstacle is that for some of the charges are not in the same row which makes it harder for me to do the average. 

    • Post Points: 21
  • Tue, Aug 5 2014 11:13 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 290
    • Points 4,668
    • MVP

    Re: Help with figure this issue

    Hi Jerry

     

    Peter's solution is not eliminating any rows at all.

    It is merely filling in any cells in column A, with the value that exists in the row above, therby allocating a Svc Procedure number to every row.

    However, the Pivot Table is counting all of the rows when calculating the average, and I suspect that you are wanting an average of only the cells in column C, where there is also an entry in Column B.

     

    If that is the case, then with 99213, E1, 99214 in E2 and 99215 in E3, the following formula should provide what you want, on Peter's file

    In cell F1

    =SUMIF($A$2:$A$100,$E1,$C$2:$C$100)/COUNTIFS($A$2:$A$100,$E1,$B$2:$B$100,"<>")

    The first part of the formula sums all values in column C where the entry in column A corresponds to the entry in E1 (including 0 and blanks which of course have no influence on the result), and then the second part counts the number of rows where column A corresponds to the entry in E1 AND cells in column B are not empty.

    Copy the formula down and you will get results of
    99213     81.024
    99214    106.481
    99215    116.384 

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 36
  • Wed, Aug 6 2014 4:16 PM In reply to

    • Jerry
    • Top 150 Contributor
    • Joined on Mon, Aug 4 2014
    • Atlanta
    • Posts 8
    • Points 168

    Re: Help with figure this issue

    Hi Roger,

    Thank you very much for your help. My last question is for instance column A has 99214 Svc Procedure with 3 charges in column C  that are below each other. Does the formula counts the 3 charges in column C that are below each other in relation to 99214?

    • Post Points: 37
  • Thu, Aug 7 2014 2:32 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 224
    • Points 3,368

    Re: Help with figure this issue

    Jerry

    The best way of seeing if a formula is working in the way in which you want it to work is to use try it out with a small amount of  data. If you use Example_2.xlsx and add Roger's formulas, you will see that the averages produced depend upon the presence or absence of the Svc Procedure in column A.

    Check out the Excel help files on functions that you don't understand. I've copied below the entry for the SUMIF function:

    SUMIF(range, criteria, [sum_range])

    The SUMIF function syntax has the following arguments:

    • range    Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

    • criteria    Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, ">32", B5, 32, "32", "apples", or TODAY().

    (Important   Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.)

    • sum_range    Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

    Peter

    • Post Points: 5
  • Thu, Aug 7 2014 4:41 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 290
    • Points 4,668
    • MVP

    Hi Jerry

    I was away from my computer all yesterday, so could not reply.
    The straight answer to your question is - YES, it does.
    As Peter has suggested, you should play around with some sample data and the formula to try and understand what is happening.

    Provided you are using Excel 2007 or above, then as an alternative, you could use the AVERAGEIFS() function.

    All of the functions with IFS at the end of their name take multiple criteria to determine which items to include.
    The syntax is different to the ???IF() function, in that the range of values to be calculated is given first, followed by each set of criteria ranges with the criteria included

    =AVERAGEIFS(range to be calculated,criteriarange1,criteria1,criteriarange2,ciiteria2, ......)

    In the case of your data, and Peters Example_2 file, it would be

     =AVERAGEIFS(C2:C100,A2:A100,E1,B2:B100,"<>")

    In English, this would be Averge the range pmt Amount (C":C100), if Svc Procedure (A2:A100) equals the value in cell E1, AND if the value in Pmt Remit Carrier Abbrev (B2:B100) is not equal to null.

     

    This produces exactly the same results as my previous formula (which can work in all versions of Excel).

    I have uploaded a file called Example_3.xlsx which shows my original forulae in column F, and this altertive Averagifs() formula in column H

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 21
  • Thu, Aug 7 2014 8:21 AM In reply to

    • Jerry
    • Top 150 Contributor
    • Joined on Mon, Aug 4 2014
    • Atlanta
    • Posts 8
    • Points 168

    Re: Help with figure this issue

    Thank you very much Roger and Peter for your time and patience. I am a work in progress with excel and you were more than helpful. God bless you all.

    • Post Points: 5
  • Tue, Aug 12 2014 4:12 PM In reply to

    • Jerry
    • Top 150 Contributor
    • Joined on Mon, Aug 4 2014
    • Atlanta
    • Posts 8
    • Points 168

    Re: Help with figure this issue

    Roger,

    Sorry for coming back with the same problem. I have attached my result using your formula and it is not giving me the an answer for all svc procedure. Please let me know if you can save me from killing myself. Thanks

    • Post Points: 37
  • Wed, Aug 13 2014 3:20 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 290
    • Points 4,668
    • MVP

    Re: Help with figure this issue

    Hi Jerry

    Sorry, but there is nothing showing up for me as any attachment.
    To attach a file here, when you are on the Compose tab, if you look at the top there is a tab called Options.
     Go there anc clcik the File / Update button, Choose your file and then scroll down to Save

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 21
  • Wed, Aug 13 2014 7:08 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 224
    • Points 3,368

    Re: Help with figure this issue

    Jerry

    It might help you understand what is going on if you split Roger's formula in cell F1
    =SUMIF($A$2:$A$24065,$E1,$C$2:$C$24065)/COUNTIFS($A$2:$A$24065,$E1,$B$2:$B$24065,"<>")
    into cell G1
    =SUMIF($A$2:$A$24065,$E1,$C$2:$C$24065)
    and into cell H1
    =COUNTIFS($A$2:$A$24065,$E1,$B$2:$B$24065,"<>")
    and then pull down these formulas for the results table starting with column E. 

    You will then see that the COUNTIFS function is returning 0 for many of the Svc Proc Codes, and of course division by zero is not allowed and so Roger's formula is returning the #DIV/0! warning.

    The COUNTIFS is returning 0 for many of the Svc Proc Codes because it includes two criteria, the second of which is that the cell entry in column B must not be empty for it to be counted.

    One possible solution is to wrap Roger's formula with the IFERROR function
    =IFERROR(SUMIF($A$2:$A$24065,$E1,$C$2:$C$24065)/COUNTIFS($A$2:$A$24065,$E1,$B$2:$B$24065,"<>"),"no countable data") 

    Peter

    • Post Points: 21
  • Wed, Aug 13 2014 8:18 AM In reply to

    • Jerry
    • Top 150 Contributor
    • Joined on Mon, Aug 4 2014
    • Atlanta
    • Posts 8
    • Points 168

    I am sorry hope that it is better. Thanks again

    • Post Points: 5
  • Wed, Aug 13 2014 8:46 AM In reply to

    • Jerry
    • Top 150 Contributor
    • Joined on Mon, Aug 4 2014
    • Atlanta
    • Posts 8
    • Points 168

    Peter,

    Thank you for all your help. I understand the concept but my issue was that let's just say for example we have svc procedures 99245 on cell E14 it shows " countable data" however there are data for that 99245. That is one of a million examples. Thanks

    • Post Points: 21
Page 1 of 2 (16 items) 1 2 Next > | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.