Medical Insurance Calculations
Jan 6, 2010
If H6 amount is greater than E8 then the answer in F8 will be the same as H6. If not, F8 will be the difference between H6 and E8.
The above will be used for the deductible amount as follows:
If patient A has a $1,000 deductible and the allowed amount for the product is only $250.00, then all of that woudl be the patient responsibility. However, if the patient has a $250.00 deducble and the allowed amount is $1,000 then the patient would only owe the $250.00 deductible.
Is there a formula that can decipher this?
View 9 Replies
ADVERTISEMENT
Mar 15, 2014
In Australia we calculate Insurance Premiums usually being a Base Premium, Plus Stamp Duty, Plus Fire Service Levy (FSL) Plus Goods & Services Tax (GST) - there is no GST on Stamp Duty, however Stamp Duty is calculated on the total of Base Premium (+ GST) Plus FSL (+ GST).
See below:
NET TO GROSS i.e. Base plus FSL, plus GST, plus Stamp Duty.
Base PremiumUnderwriter provides
Key in blue box onlyBase785.68
StateSA
TypeMotor
FSL0% 0
GST10% 78.57
Stamp11% 95.07
The total premiums is Total Premium959.32
We want the following formula/calculation to be able to just load a Gross/Total Premium figure and have excel calculate the base premium i.e. Total premium - Stamp Duty - GST - FSL
GROSS TO net
Total Premium
Key in Total Premium00
Stamp duty $- 11%
GST $- 10%
FSL $- 0%
The base premium isBase0.00.0.00
Formulae must allow for changes in % rates above (i.e. 11% stamp duty and 0% FSL may be different in each state and a potential change in the GSTrate) as these differ from each state and we need to show the actual amount of each component i.e. Stamp Duty, GST and FSL in the calculation.
Fire Services Levy (FSL) is calculated on a Base Premium plus GST (10%) - FSL varies from State to State, so we need to be able to input the relevant State % rate, GST is calculated on Base Premium (ex GST) plus FSL (ex GST), Stamp Duty is calculated upon Base Premium (plus GST), FSL (plus GST),
provide the respective formulae so that we can do this calculation ourselves using different rates and Total Premium figures.
View 2 Replies
View Related
Feb 26, 2014
Attached is an example of an excel spreadsheet I have which details all the referrals created within my organisation over 2013. I want to be able to create two excel formulas which basically looks at a duplicate tag and then determines if the duplicate referral was created within 30 days of other referrals created for the same patient and then another formula to check to see if the duplicate referral is for the same medical condition. Also if possible I would love a third formula which would say check duplicate, 30 days and same condition and enter a "Yes" in a forth column.Referrals.xls
View 8 Replies
View Related
Jun 20, 2009
I price life insurance policies and need a formalu for the following new Fund:
If the sum of the first two years of premium added to 10% of that total is equal to or less than 25% of the face amount, the case fits into the general parameter.
Here is what I have, but it isn't working:
=IF((U2+V2)*10%)+(U2+V2))
View 9 Replies
View Related
Nov 1, 2007
I have a spreadsheet where data is pasted on from spreadsheet supplied by various external companies. One of the main problems we're having when validating the data is an incorrect NI Number and I'm hoping to find a way of getting this automated.
What I would like to do is, when a block of text is pasted into our template, a block of code to look through the selection and check column S for a valid format NI no (eg AB123456C). I have some code (below) that works when changing an individual cell, but nothing that works when pasting in blocks of text.
Private Sub Worksheet_Change(ByVal Target As Range)
stringvalue = Target.Value
statuschange = 0
Application.EnableEvents = False
'Check for text only cells
If Target.Column = 19 Then
View 9 Replies
View Related
Feb 9, 2010
if you look at attachment, in Cell J2, if cell I2 is greater than 2 minutes but less than two minutes, the I need the value of I2 in Cell J2 otherwise 0:00:00 then K2 between 3 & 4 Mins and so on.
View 5 Replies
View Related
Jan 27, 2010
I am making a spreadsheet that tells us the following information about prints that we do:
View 6 Replies
View Related
Feb 2, 2010
I have attached a sample file. All the data is inserted into this file via a text file, except one column "G". Someone in this forum was kind enough to help me in being able to include the city name 'G' to a parcel number 'A'. However, the page is constantly trying to complete 'Calculations' and won't let me do anything without first hitting 'Control Break'.
This is fine except that when I try to save the file into .txt, I don't have the option of 'Control Break' and I have to end up closing the file. As the 'real' file has over 100,000 rows, nothing happens very fast. I have tied changing the 'Calculation Options' but that doesn't seem to change anything.
View 7 Replies
View Related
Jul 11, 2012
Is it possible to have an input box for a range of calculations and then in my formulas set the range to anchor + variable ?
Something roughly like:
Range = 20
Product( F13:F & Range_Variable_Cell_Value)
View 2 Replies
View Related
Mar 19, 2007
I'm working working with this spreadsheet that is moving incredibly slow. Every time I enter anything, it takes anywhere from 10 seconds, to a couple minutes to calculate and let me proceed. It is a pretty big file (4.60 MB), but I also work with another spreadsheet that is a little smaller (2.95 MB) that has never taken more than a fraction of a second to calculate anything. What could I do to spead up the spreadsheet?
View 9 Replies
View Related
Sep 15, 2007
My problem is the following:
I'm trying to model straight line depreciation over 5 years for certain investments. This could for example be done like this: ....
View 9 Replies
View Related
Sep 2, 2008
Does anyone know of a site or some code that can make changes to add some items to the status bar calculations?
Such as right now you have Sum, Count, Count Nums, Max, Min... just to name a few.
I'd like to be able to add some other ones down there if it can be done. I'd like to count #N/A's or possibly sum only positive numbers? I can do it with a quick keyboard shortcut and message box with a macro, but I'd like to just be able to highlight a range and have it show up like sum or the others do.
Just thought I'd psoe the question.
View 9 Replies
View Related
Feb 21, 2009
im in the process of designing a userform but it needs to have some calculations in it, and im lost on how to do it if anyone can help
ie
in textbox1 there is a value of 5 and in textbox2 there is a valuve of 10 and in text box 3 i want to appear textbox1 multiplied by textbox2 so 50 should appear
this is a basic example but as soon i have mastered that i can adapt everything else to my userfom
will the above be done automatically as soon as i enter values or would i have to "make the calculation" via a button or something
and also i want to have a enter button or something like that that copies the data that i have put in the various text boxes to the excel spreadsheet and clear the userform cells
phew thats best i can explain any questions just reply to this post as per the norm
View 9 Replies
View Related
Feb 21, 2010
I have inventory... with starting product at a certain cost, received product at a new cost, and used product. I want to assume that we are using FIFO.
What I need is a total cost (what I have paid) for what is sitting in my freezer.
Column A is START (5)
Column B is RECEIVED (6)
Column C is USED (2)
Column D is END (9) or (A1+B1-C1)
Column E is OLD COST ($12.20) cost per unit of those 5
Column F is NEW COST ($13.50) cost per unit of the 6 i got in
So I need in Column G a FIFO formula for total cost of what I have in the fridge.
View 8 Replies
View Related
Mar 19, 2007
can i hide all calculations inside my excel document? I wish to provide some excel worksheets but must protect the calculations performed for privacy reasons. I would still like to enable clients to sort tables, change pivot tables, etc but not to see what calculations are used.
View 3 Replies
View Related
Oct 4, 2007
I dont know if that is the correct title to use but here goes. I am trying to help my friend with some work that he is struggling with.
We have a model where we can change the % of the Service Level in field E8 and it will tell us the number of people required within field E17. Is their a way we can reverse this by creating another spreadsheet where we could put in the number of people we have for it to tell us the service level that would reach?
View 9 Replies
View Related
Jan 19, 2008
I have a Userform set up and I have a combo box which I have filled with options using the With Combobox.add method with 5 choices; Minutes, Hourly, Weekly, Monthly & Yearly.
I want these choices if slect to represent a value to make this easier say if minutes is slected the value would be 1, hours would be 2 etc..
I want this choice to be stored as a variable say time, which can then be later used in a calculation which is run when I run my main program based on this selection.
View 4 Replies
View Related
Apr 29, 2014
I have a spreadsheet that I am trying to have automatically calculate a total based on certain criteria:
I want the amount under the per diem amount column W to return a value based on if P is entered in column V. If this is for partial it will depend on departure and arrival times. If departure is after 6:00 AM no breakfast per diem is paid, if departure is after 11:00 AM no lunch is paid. If arrival is before 2:00 PM no lunch. If arrival is before 7:00 PM no dinner. If the user enters a 1 or 2 in column U, 1 uses out of state per diem breakdown located in cells I38:I40. In state uses a 2 and is located in cells E38:E40.
View 14 Replies
View Related
Feb 12, 2014
I want to pay different commission rates for different levels of sales...
IE nothing if sales are under 250,000 per year.
5% between 250,000 and 500,000,
8 % between 500,000 and 750,000
10% between 750,000 and 1,000,000
12.5 % between 1,000,000 and 1,250,000
and 15 % over 1,250,000
The issue that im having trouble with is that if the sales guy brings in 1,500,000 in yeary sales he would be paid some at 0%, some at 5% some at 8% some at 10% , so at 12.5 and some at 15%
How do i calculate that? I have included a excel spreadsheet.
View 6 Replies
View Related
Feb 6, 2010
Attached is a layout that I am trying to get working.
the 1st tab is the input sheet, I want to be able to post input on that sheet and have it transfer to the other tabs in the appropriate fields.
I'm having a problem mostly with the vlookup to get the data into the right cells on the other tabs.
View 2 Replies
View Related
Sep 17, 2013
I have A1 that contain numbers, B1 that contain number, and i want to multiply those 2 and at the end of result i want to add zeros on front. Let me give you an example:
A1 field has data :00.375B2 has data: 6.49 C1 has the multiply result of A1+B1: 2.43 (But i want when the calculation is done and have the result 2.4 to add 2 zeros on front and become: 002.43 or if the result is for example: 65.20 to add 1 zero on front and become 065.20 , and if the result is 102.20 do not add anything on front.
So in total i need to have the 5 digits of number.
View 4 Replies
View Related
Feb 19, 2014
I am creating a userform that has multiple calculations in it. I understand how to do this in Excel but I have no idea what the order of operations would be with a user form. (UoM Cost) will = Unit Price / Quantity). The (Ext Cost) field would equal [Quantity x Waste% x UoM Cost (that needs to be calculated before) + Unit Price. (Cost Per Each) would = the "Ext Cost"....that needs to be calculated before / Quantity - Waste%.
Once I have these calculations, then need to click a button to either reset the user for for another record (or if I mess up) and a button to save the record to the excel spreadsheet in the background.
View 8 Replies
View Related
Mar 2, 2014
I would like to implement a pricing tool where by if you select certain boxes the price will be increased or decreased.
For example. If chk_UtilA is selected on the attached then the target price will be multiplied by 10%.
View 6 Replies
View Related
Nov 25, 2009
when I powered up my workstation, the workbook has reverted back to calculating upon startup. I have provided a sanitized version of the workbook. I think the issue with the calculations during startup may not be apparent to you due to your inability to access the Access database this workbook extracts data from. I attached a test database which should hopefully work correctly with the workbook to demonstrate the calculation issue. Extract and save the test database in c:emp. That is where the workbook import queries will look for it.
The live workbook is much larger and on a remote server so the calculations take a lot longer than what you might see here.
View 4 Replies
View Related
Aug 6, 2006
if sales target is say 1000
if they dont reach 60% no commission payable
if the reach 60% and over to sales target they get 3%
if they get over target they get 5% of everything over target
View 9 Replies
View Related
Dec 17, 2008
I have a specific problem on irr calculations. In the excel file, I have following data.
date of investment - 1st May 2008
Investment Value - 1000000
Investment close date - 15th Dec 2008
Closure value - 1055000
I want to calculate IRR for the investment for the days the money got invested.
How do I calculate this in excel.
View 2 Replies
View Related
Dec 17, 2008
I want to be able to sum across a worksheet the products in individual rows of a $ rate and a qty, without doing this for each column and then adding them.
for example: I might have in B1 '$100' and in B2 '3', then in C1 '$200' and C2 '6' and so on. What formula can I put in A3 to sum B1*B2 plus C1*C2 and so on?
View 2 Replies
View Related
Oct 26, 2011
I need C3 to auto calculate depending on the value of A3 and B3.
Example:
If A3=Minor and B3=Possible Then C3=Low L4.
So there are 25 unique possibilities depending on what A and B =
Is there a way to get around this or will i have to resort to a Macro??
This data is based on the Matrix Below.
ConsequenceMinorModerateSevereMajorCatastrophicLikelihoodAlmost CertainHigh
(H10)High (H13)Extreme (E17)Extreme (E23)Extreme (E25)Quite PossibleModerate (M6)High
(H11)High (H14)Extreme (E19)Extreme (E24)PossibleLow (L4)Moderate (M7)High
(H12)Extreme (E18)Extreme (E22)UnlikelyLow (L2)Low (L5)Moderate (M9)High
(H16)Extreme (E21)Very UnlikelyLow (L1)Low (L3)Moderate (M8)High (H15)Extreme (E20)
View 6 Replies
View Related
Mar 8, 2012
As a complete novice....I want to:
1. Create a list of 3 different types of projects
2. Write three different types of calculations that should be undertaken dependent on the type of project. Each will be in a separate worksheet.
3. Make sure once I've checked the project type I apply the right calculations that match the project type
4. Summarise the results of the calculations against each of the projects
Can I do this just in excel or do I need to use macros?
View 9 Replies
View Related
Jan 14, 2014
A customer has an overdue account, we will say in the amount of $500.00. We offer various discounts, 30%, 20%, 0% depending on the circumstance.
What I had previously worked flawlessly, that is until the higher ups changed how things had to be calculated.
Previously, we would take the amount owing and apply the discount (ie 20%) which would become the new amount due ($400).
We would then take the discounted amount and separate it into monthly payments:
$400.00
1st payment 60.00
2nd payment 60.00 (and so on)
6th payment 60.00
7th payment 40.00
Done.
The new way they want it calculated is (Same situation $500 owing, 20% discount).
$500.00
1st payment 60.00
2nd payment 60.00 (and so on)
6th payment 60.00
7th payment 60.00 *
8th payment 60.00 *
9th payment 20.00 *
Done
(*these would be the discounted amount, it would be stated that these payments would be removed if payments are made on time and in full yadda yadda).
The trouble I am having, is figuring out a way to tell excel to flag the specific payments. The reason behind it (i guess) is so that if a client becomes delinquent, the full amount owing would be sent to collections, not the discounted amount owing.
View 1 Replies
View Related
