Discussion New to excel: Calculator or estimator in Excel
https://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3055382#M129034
<P>I am trying to create from the Medicare fee schedule which has CPT codes and a payment amount.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="BargieGirl_0-1641744885552.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/338190i5C1E03F0C6C8E630/image-size/medium?v=v2&px=400" role="button" title="BargieGirl_0-1641744885552.png" alt="BargieGirl_0-1641744885552.png" /></span></P><P>a cost estimator. I can use the see the FS(fee schedule) that sheet is on or a new sheet. I need to be able to type in the CPT code and the amount pull up(par amount). Then I can do the formula of 20% of that amount and the patient will know what they are responsible for. My problem is what function or how do I pull that information into a new cell. Example: CPT 60100 has the amount of $110.98, how can I type the CPT code in one column and it pull up the amount in a second column? </P><TABLE width="280"><TBODY><TR><TD width="152">TYPE CPT CODE</TD><TD width="64">60100</TD><TD width="64">#NAME?</TD></TR></TBODY></TABLE><P> Please advise,</P>Sun, 09 Jan 2022 16:21:17 GMTBargieGirl2022-01-09T16:21:17ZNew to excel: Calculator or estimator
https://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3055382#M129034
<P>I am trying to create from the Medicare fee schedule which has CPT codes and a payment amount.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="BargieGirl_0-1641744885552.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/338190i5C1E03F0C6C8E630/image-size/medium?v=v2&px=400" role="button" title="BargieGirl_0-1641744885552.png" alt="BargieGirl_0-1641744885552.png" /></span></P><P>a cost estimator. I can use the see the FS(fee schedule) that sheet is on or a new sheet. I need to be able to type in the CPT code and the amount pull up(par amount). Then I can do the formula of 20% of that amount and the patient will know what they are responsible for. My problem is what function or how do I pull that information into a new cell. Example: CPT 60100 has the amount of $110.98, how can I type the CPT code in one column and it pull up the amount in a second column? </P><TABLE width="280"><TBODY><TR><TD width="152">TYPE CPT CODE</TD><TD width="64">60100</TD><TD width="64">#NAME?</TD></TR></TBODY></TABLE><P> Please advise,</P>Sun, 09 Jan 2022 16:21:17 GMThttps://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3055382#M129034BargieGirl2022-01-09T16:21:17ZRe: New to excel: Calculator or estimator
https://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3055792#M129092
<P>Hi <LI-USER uid="1270084"></LI-USER> </P><P> </P><P>the function that you can use for this is VLOOKUP.</P><P> </P><P>For your example, if the CPT schedule is in Sheet2 and you type the CPT code in cell B1 on Sheet1, you can use this formula </P><P>=VLOOKUP(B1;Sheet2!$B$2:$D$7;3;FALSE)</P><P> </P><P>Just note that you have to adjust the lookup-range according to the real number of lines in your table. So instead of $B$2:$D$7 it could be $B$2:$D$100, if you have 100 lines.</P><P> </P>Mon, 10 Jan 2022 07:00:00 GMThttps://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3055792#M129092Martin_Weiss2022-01-10T07:00:00ZRe: New to excel: Calculator or estimator
https://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3056118#M129150
<P><LI-USER uid="48469"></LI-USER> here is the formula I used. =VLOOKUP(K5,$B$2:$D$11010,4,FALSE) I tried to do it on the same sheet. So basically, what I am trying to do is to be able type a cpt code in one cell and then the cell next to it the pmt comes up from a formula or Vlookup. I wish I could send you the spreadsheet and you see if you can make something.</P><P>Still doesn't work. </P><TABLE border="0" width="192" cellspacing="0" cellpadding="0"><TBODY><TR><TD width="64" height="20">CPT</TD><TD width="64">PMT</TD><TD width="64"> </TD></TR><TR><TD height="20"> </TD><TD> </TD><TD> </TD></TR><TR><TD height="20">60500</TD><TD>#N/A</TD><TD> </TD></TR><TR><TD height="20">78800</TD><TD>#N/A</TD><TD> </TD></TR><TR><TD height="20">76536</TD><TD>#N/A</TD><TD> </TD></TR><TR><TD height="20"> </TD><TD> </TD><TD>TOTAL</TD></TR><TR><TD height="20"> </TD><TD> </TD><TD>X20%</TD></TR></TBODY></TABLE>Mon, 10 Jan 2022 14:46:37 GMThttps://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3056118#M129150BargieGirl2022-01-10T14:46:37ZRe: New to excel: Calculator or estimator
https://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3056830#M129272
<P>Hi <LI-USER uid="1270084"></LI-USER> </P><P> </P><P>your formula has one error:</P><P>you refer to $B$2:$D$11010, which covers 3 columns (B, C, D). But in the next argument, you refer to column 4, which is not possible. Instead, it should be</P><P><SPAN>=VLOOKUP(K5,$B$2:$D$11010,3,FALSE)</SPAN></P><P> </P><P><SPAN>If you still do not get a proper result, the reason might be the data format: It looks as if the values in column B are text (what you can see on the little green triangle in the left top corner of each cell).</SPAN></P><P><SPAN>Just make sure, that the cell K5 is uses also text format for the entry. Otherwise the vlookup tries to find a number in a range of text. This will not match, even if the values look the same.</SPAN></P><P> </P>Tue, 11 Jan 2022 07:31:08 GMThttps://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3056830#M129272Martin_Weiss2022-01-11T07:31:08ZRe: New to excel: Calculator or estimator
https://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3062977#M129961
<P><LI-USER uid="48469"></LI-USER> Thank you! I managed to make it work. I had to get rid of the green corners and also to make sure my cells were text. </P>Sun, 16 Jan 2022 15:42:45 GMThttps://techcommunity.microsoft.com/t5/excel/new-to-excel-calculator-or-estimator/m-p/3062977#M129961BargieGirl2022-01-16T15:42:45Z