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?


Formula To Convert Gross Insurance Premium To Net Insurance Premium?

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

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

Mark Duplicate Referrals Which Occur With 30 / 60 / 90 Days For Same Medical Condition

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

IF Formula Used To Price Life Insurance Policies

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:


View 9 Replies View Related

National Insurance Number Validation On Blocks Of Data

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

Time Calculations.?

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

Certain Line Only Calculations

Jan 27, 2010

I am making a spreadsheet that tells us the following information about prints that we do:

View 6 Replies View Related

Turn Off Calculations

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

Set Range For Calculations Without VBA?

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

Slow Calculations

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

Depreciation Calculations ...

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

Changes To Status Bar Calculations

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

Userform Calculations

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


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

FIFO Calculations

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

Hide All Calculations?

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

Reverse Calculations ..

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

Combo Box With Calculations

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

Multiple Criteria For Calculations

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

Multiple Commission Calculations?

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

Time Sheet Calculations

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

Add Zeros After Formula Calculations?

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

Multiple Calculations In Userform?

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

Calculations Based On Checkboxes

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

Stop Calculations Upon Startup

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

Sale Commissions Calculations

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

Irr Calculations / Formula For Investment

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

Summing Multiple Calculations

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

Auto Calculations For A Matrix

Oct 26, 2011

I need C3 to auto calculate depending on the value of A3 and B3.


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

Different Calculations For Different Data Types

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

Apply Discount After Calculations?

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:

1st payment 60.00
2nd payment 60.00 (and so on)
6th payment 60.00
7th payment 40.00

The new way they want it calculated is (Same situation $500 owing, 20% discount).

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 *

(*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

Percent Change Calculations

Dec 8, 2006

I want to calculate percentage changes, but sometimes my values are negative. Using the traditional (latest-first)/first I'm getting incorrect percentages because of the negative values. How can I write one formula that corrects for this?

View 9 Replies View Related

Cross Matrix Calculations

Jan 8, 2007

I have 2 tables. One has a listing of items and the work centers that they use and their associated hours. The other has the items and the amount we will produce each month.

What I need is to be able to calculate the number of hours for each item by month and then make a summary so I can see the capacity needed for each item, work center by month.

I have used the VLOOKUP and it works but when I overwrite the second table with new data the calculations get messed up

View 11 Replies View Related

Performing Calculations On My Comments!

Feb 26, 2008

i've managed to get a set of cells to show (with comments) what the last weeks data looked like. (so cell value = this week, comment contains last week)

What i now want to do is to use conditional formatting to highlight any major differences between the figures... so:

My comment i'm sure is stored as text in the format:

Previous value was

So the first thing i'd need to do is to to strip out the text at the start, and then perform the calculation.

View 9 Replies View Related

Inventory On Hand Calculations

Sep 22, 2009

I have a simple inventory spreadsheet that is doing everything I need minus one function. I have not been able to figure out how to make this work yet, maybe I can get some help. We remodel apartment complexes. I purchase material and hand it out to the contractors. I am using one page to track all movement of the items, but I have another page that I want to do that math and tell me what I should have on hand. Example: (On sheet1 "movement") I hand out 20 outlets. On my inventory on hand sheet I have a line that says "Outlet", the current quantity is 40. I have a drop down list so that on the Movement page I select "Outlet" in column A and in Column B I put in the quantity. I want to keep a running total on my IOHand page of everything, so that it would automatically subtract the 20 I just handed out and leave my IOH as 20. I have about 100 items on my IOH page. I assume I need a formula in the quantity cell of my IOH page that will search the Movement page and look for a match to what is in column A and Sum all of my "outlets" or "sinks" into the right cell.

View 7 Replies View Related

Speed Up Vlookup Calculations

Oct 25, 2008

I have been assigned to speed up vlookup calculations that reference several workbooks. The spreadsheets currently take a very long time due to the enormous amount of calculations.

A typical example of the vlookup formulas is on the production tab of the attached excel file. We use 12 different workbooks that refer to each other and up to about 10 worksheets in each workbook.

I've read a little bit about optimizing vlookup and using other methods, but I'm not sure how to apply it to my case where it refers to other workbooks. Any help you could provide in giving me a place to start would be greatly appreciated (i'm not even sure if excel formulas or vba code would be the best approach).

View 6 Replies View Related

Reverse Mortgage Calculations

Jun 2, 2006

I set up a formula knowing only two variables?

1. Assuming I know how much per month one can pay in total, and
2. The percent interest rate

... I need the total amount that can be financed.


A. $3,900 maximum per month
B. 5.00% interest rate
C. = x (the amount to be financed)

View 8 Replies View Related

Calculations Between Two Pivot Tables

Aug 3, 2006

I wonder if it is possible to make calculations between two pivot tables which have the same number of columns and rows and put the outcomes in a third pivot. For example Pivot 1 contains revenue data, Pivot 2 contains cost data and Pivot 3 provides the calculated outcome of revenues minus costs.

View 7 Replies View Related

Time And Cost Calculations ..

Aug 14, 2006

I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit.

The source data I have is;

1 - Start time and end time of the job

2 - Any breaks taken during the job

3 - The number of staff it took to do it

4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates

5 - The number of punnets packed.

With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning 7.50 an hour worked for 1 hour 15 mins at a cost of 0.39

View 2 Replies View Related

Restrict Calculations By Sheet

Dec 13, 2006

My 10 MB excel file has calculation mode always automatic. It has 100 worksheet. Only a few of these worksheets are enable for calculation, for performance optimization.

Therefore, I use the macro: Worksheets("sheet1").EnableCalculation = False
When needed, I do: Worksheets("sheet1").EnableCalculation = True

Unfortunately, from time to time, the calculation does not work. By chance I realize that by insterting a cell in this worksheet, the EnableCalculation became active. My problem is that I feel this is happening randomly, depending on worksheet, maybe depending on users or on sequences of entries.

View 4 Replies View Related

SUMPRODUCT Causes Slow Calculations

Jan 5, 2007

I made one excel file with 9 sheet. there are used sumproduct formula in 200 rows.

i am not used any vba macro for this file.

i am used sumproduct formula link to other files and other sheets.

my file size is 1.80 mb but when i am enter new data in file then file going in process of calculation and calculate all forumula it take some time. my file options calculation is automatic but for calculation it take time and i have to wait for that for Process.

There are any macro for calculation so my file work fast and don't want to wait for calulation process .

This file is not open like other files. it is take a time in update the link or calculation.

View 9 Replies View Related

Displaying Cell Value But Don't Use In Calculations

Apr 4, 2007

I have a row of raw quality control data, but some are not in control and should not be used in the calculation of statistical data. There are not a lot of them, so I can manually select these (I use a red fill color for the cell to signify bad data), but I would like to be able to keep these data displayed but not have them included in the calculations.

View 3 Replies View Related