Tracker
HOME    TRACKER    Excel

# Medical Insurance Calculations

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

Related Forum Messages:
IF Formula Used To Price Life Insurance Policies
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))

National Insurance Number Validation On Blocks Of Data
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

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

FIFO Calculations
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 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.

Reverse Calculations ..
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?

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

Turn Off Calculations
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.

Slow Calculations
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?

Time Calculations.?
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.

Depreciation Calculations ...
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: ....

Hide All Calculations?
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.

Changes To Status Bar Calculations
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.

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
xxxxx

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

Cross Matrix Calculations
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

Inventory On Hand Calculations
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.

SUMPRODUCT Causes Slow Calculations
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.

Summing Multiple Calculations
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?

Stop Calculations Upon Startup
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.

Sale Commissions Calculations
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

Percent Change Calculations
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?

Time Sheet Calculations
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.

Combo Box With Calculations
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.

Mortgage Calculations With A Difference
Scenario - I started doodling with buying a property to let and then started thinking about adding the "shortfall" between the required bond payment and the income generated by the property back onto the loan each month as the shortfall arises. Let's assume that the financial institution will "pay" this shortfall from a previously agreed upon additional amount available on the property - understand?

While there is a shortfall between the income generated by the property and the bond payment, insurance, taxes etc., the outstanding capital will obviously increase as each new month arises and each specific shortfall is added onto the outstanding capital (loan at the bank).

Once the shortfall turns into surplusses, the surplusses are in turn paid into the account until the bond is paid in full.

With a normal, say 15 year, bond, the interest payments and principal payments of the constant mortgage payment (assume fixed interest rate) nicely works out to a balance of 0 in month 180, but not in the scenario I describe.

What happens in the scenario I described above is that, if I keep the payment constant (=PMT(and the rest - calculated on the original term and the original investment) each month's deficit is added to the outstanding capital but the payment stays constant. I want this payment to remain constant throughout the bond.........

#VALUE! Error With Date Calculations
I have 2 columns of dates and would like to find the difference in 2 dates in column a and b for example. when I use the formula a2-b2 I get #Value! or # Name? in these cells. The cells appear to be in format dd/mm/yyyy. I tried formatting them to mm/dd/yyyy, but the dates are not changing at all. I have quite a few of these spreadsheets so it won't be practical to retype each cell individually.

Calculations Via True And False
I am currently looking at the workings of a spreadsheet designed by someone else.
Within this sheet, a cell has the formula that , once broken down, equates to:

=100*TRUE (Cell returns a value of 100)

And another that equates to :

=100*FALSE (Cell returns a value of 0)

Am I correct in thinking that TRUE always equals 1, and FALSE always equals 0? I think this is right, but just need someone to confirm.

Reverse Mortgage Calculations
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.

Example:

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

Speed Up Vlookup Calculations
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).

If Function Multiple Calculations
how to make this work. I am calculation age and service for a spreadsheet.
=IF(OR((E12>=60)*(F12>=10), (E12>=55)*(G12>=75)),"Y", "N")

Loan Amortization Calculations
I'm working with a loan amortization worksheet (downloaded from Office Online). Unless I don't understand correctly, the date formula on the worksheet doesn't calculate the way I need it to. I'm not totally sure what the formula they use is doing. It does use a lot of named ranges.

If a user inserts the total "Number of Payments per year", then I want the date to return the proper payment date.

For instance, If the start date is 1/1/07 and the number of payments per year is 24 then the payment dates should be like

1/15/07
1/31/01
2/15/07
2/28/07

It should be the 15th and the last day of the month.

If I put 52 as the number of payments then I want the formula to set the payment dates to every Friday.

I'm still learning formulas so bare with me guys. Attached is the worksheet.

User Interface For Calculations
I am trying to create a user pop-up box that can accomplish the following:

a. Pop up box that requests an input for a particular machine number, type of error and also a particular calculation type (STDEV,Average,etc).

b. The result will be displayed either in a tabular format or if applicable, a chart

I have 10 worksheets where each worksheet is a different mahcine ID. Contained within the worksheets is various data-sets(Data listed by week). I am trying to enable my mangers ability to not have to look through my data or choose a worksheet within my workbook. I would rather have a input screen that would give him a choice.

Restrict Calculations By Sheet
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.

Calculations Between Two Pivot Tables
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.

Time And Cost Calculations ..
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

Populating A Table For Gear Calculations.
I have a list of numbers (each corresponding to a different available gear). I have four columns and am trying to populate those columns with all the possible variations without using the same gear twice in any one row. Is there any way of doing this automatically or with a script or something? There are 16 different gears so it could be rather time consuming to input all variations manually.

The attached worksheet has what I am trying to do begun. The possible number of iterations or variations is apparently enormous and am hoping for an automated way to populate those cells.

Weekly Time Sheet Calculations
I need to calculate the total hours worked for a series of 22 locations. Each Location has weekly allocated hours ranging form 0 to 80.

For example:
User1 worked 4 Hours on Monday at Location1 and 4 Hours at Location2.
User2 worked 4 Hours on Monday at Location1 and 4 Hours at Location3.

I am assuming I will need to use VBA for this calculation.

Evaluate() For Loop Less Range Calculations
I'm trying to get my head around using this syntax for EVALUATE() to allow an entire range of values to be changed all at once instead of via loop.

This macro works:

Sub TruncateValuesInSelection()
'JBeaucaire 2/23/2010
'Select a range and then run the macro to truncate values without looping
Dim Cell As Range

With Selection.Cells
.Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),TRUNC(" & .Address & ",2))")
End With.........

Ignore Number Values In Calculations
I've tried changing the cell format of some number values to text, so the numbers are not read and calculated in to certain formulas, but they're still included in the calculation. I need all values to display in the spreadsheet, but I need exclude a range that would vary.

Is it possible to just change the cell format, so numbers are ignored by a formula?

Employee Time Schedule, Calculations
I have tortured the cells into producing the output I want...

Namely time(s) in and out, with the hourly totals - subtracting 1/2 hour
for shifts over 6 hours.

I however have a couple of questions about glitches the approach I am using give me.... I will outline as best I can: wish I could upload a sample...

1) the user inputs (in the time format) the starting and ending times for the individual ie. 9 A = 9:00 AM ... 1:30 P = 1:30 PM simple

2) after the user inputs the total formula for each day is calculated:

=IF(D3="","",(IF((D3-C3)>.25,((D3-C3)-0.020833),D3-C3)))

That leaves the cell null if there is no time in the day...
If there is time, checks to see if more than 6 hours...
If so, deducts 1/2 hour, else does the difference calculation
This does work, but is it the elegant or simple way? recommend?

3) At the bottom of each day totals are calculated by this :

=Text((SUM(E5:E36)),"[H]:mm")

This also works to sum the individual totals to the bottom
But is this the best way to work with the times?

4) the individual then inputs the Actual Hours worked by the team...
And the sheet does a variance calculation between actual hours
Worked vs. Scheduled hours...

=TEXT((D38-D39),"[H]:mm")

Now this is a problem...cannot do negative times....need help!

5) Am having a problem with totaling the individuals weekly total using ...

=TEXT((E3+I3),"[H]:mm")

Prevent Calculations Upon Workbook Open
I have this code in my ThisWorkbook module:

Private Sub Workbook_Open()
With Application
.Calculation = xlManual
End With
End Sub
I want it to open the workbook without going through calculations (which take a long time)...

It sets to Manual, but still re-calcs upon opening...

Automating Data Import And Calculations
I have a huge database of textual files all of which have the same format - space separated text files. I need to find the average of the last column of all these files.

Each file is named uniquely. Is there an stress-free way to set up an automated procedure to go through each of these files automatically and to record the average of the last column along with the name of the file. I attached a few text files - these are daily prices of a few securities. The last column is Volume. I need to find and record the average of this column for each of these files.

The text files are here:
[url]

Time And Cost Calculations Per Minute
I have costs per minute and call times in seconds

How do I work out the cost per call in Excel?

It's probably something very simple, but I just can't work it out. No matter what I try, I can't get it right

Get The Milliseconds To Another Cell For Making Some Calculations
I set the time format to hh:mm:ss.000 to show also the milliseconds.
How can I get the milliseconds to another cell for making some calculations?

Stop The Calculations When The Workbook Opens
I need to stop the calculations when the workbook opens. I have tried

Irr Calculations / Formula For Investment
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.

Working With Dates And Making Calculations
I have the output of my database in an excel file and one of the columns is the date of instalation (of a PC in this case) and I am trying to use the date of instalation to calculate the date of RENEWAL (we have a set life cycle for our machines and so we calculate in months when its due for an upgrade).

I think just a simple formula will work. I have attached an excel file with some example information.

In column B I have the computer type(this will determine how long of a life it has), In column D we have the instalation date which is the starting date (the start of its life cycle). Here are the variables.

HW/PC DESKTOP = 1/7/5
HW/PC DESKTOP CAD SP = 1/7/3
HW/LAPTOP = 1/7/3
HW/LAPTOP HIGH-END US = 1/7/3
HW/LAPTOP ULTRA-LIGHT FR = 1/7/3

And finally, in column F I would like to display the life cycle it has and then in column G have the exact date of renewal.

Is A Single Formula Better Than Intermediate Calculations?
I come from a (procedural) programming background, where we perform operations step by step. I tend to use intermediate calculation columns, with only 1 or 2 calculations per formula, or, failing that, use a UDF. I find this easier to debug and maintain.

The solutions I see tend to give the result in one formula. This reminds me of the functional progamming languages I used years ago. I'd like your opinions on what is best-practice, or common practice, in relation to constructing formulas, so that I can improve my development.

Percentage Calculations Based On Times
i've been viewing different formuals, and have to admit there is alot more to excel than I originally thought, and totally stumped as to what and how to go about my problem.

I have a problem, and it mainly stems from my employer not paying me correctly (and pretty much the other 1600 odd employees encounter the same issue too on a semi-regular basis).

To give you a run down, this is our complexed pay system

Mon-Fri, if you work up to 7h36m you get 100%, then 150% until 10h36 and then 200% after 10h36m working time
Sat, if you work, its 150% upto 7h36m and then 200% there after
Sun, double time all shift
But to throw in some complicated things, here are some more items that need to be considered.
If you work from one day into the next (say fri into sat, and sat into sun and sun into mon, then after midnight, it then goes to that days rates)
If you work your day off, say a weekday, your then paid the eqivelant of saturdays rates unless you were called in straight away which then is sundays rates
If you work your day off, say its a saturday or sunday, then its 200% for the whole shift.
And then public holidays, its 250% upto 7h36 and 300% thereafter

So as you can see, its pretty complicated, and my employer (a government employer) can't seem to get the right software to pay us correctly, nor can the poor employee sometimes work out if they've been paid correctly or not.

So, this is what I have so far:
columns: Day, Type (normal day or work day off or work day off immediate), On1, Sign Off1, Sign On2, Sign Off2, Sign On3, Sign Off3, Working Time, Paid hours
(for instance its my normal rostered monday, I start at 12pm, lunch at 5, return at 545 then finish at 915pm, which equates to 8h30m working time and 8h57m paid time) - the 3rd sign on and sign off is if we do extra work beyond our shift.

There are other things aswell like allowances, but thats for another day, and not as urgent as what i'm really looking for.

Now if anyone can understand what i've just written above, your doing pretty good (even some of the most experience employees still don't understan our pay system), and anyone able to produce a formula for what i'm looking for, i'd be exceptionally grateful, and probably the 1600 odd other employees too who seem to get shafted occassionally.

Calculations On Multiple TextBox Values
having a different calculations performed within a Textbox on a Userform depending on the selection made in a Combo Box. This was answered here.

Perform Calculation In Textbox On Userform

I have another question regarding this problem, however the thread is closed so I have posted a new thread.

The original question was answered and the solution works very well,however I now need to be able to use the selection from two (2) comboboxes to initiate the various calculations in a similar vain.

have a look at the original thread to get an idea of what I am looking for here.

I have tried many configurations of various code, all of which works to an extent buts which falls over in certain circumstances.

Geometry Formula For Circle Calculations
See attached photo. I need a formula to calculate the length of line “c1”. I know the diameter of the circle, and the number of segments. In the photo there are 8 segments but this will change.

Different Calculations Based On Variable Inputs
If I input any FX pair (Column B) without ???JPY in it, then the formula in Column K produces the correct result. Formula is: eg: If Column B = EURUSD pairs and the like: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*10000.

This formula will produce a +ve or -ve result in points (PIPS) based on the values of E3 and F3. (eg. E3 = 1.2800 & F3 = 1.2750. If C3 = L then the result is -50. Conversely if C3 = S then the result is 50.)

When I input the JPY pairs in Column B, I need the formula to calculate the same way but multiply by 100 (not 10,000). This is because all non JPY cross pairs have 4 decimal places and JPY cross pairs have 2 decimal places. (eg. 1 PIP in EURUSD = .0001, 1 PIP in USDJPY = .01).....

So if Column B = USDJPY the formula would need to be: =IF(C3="L",F3-E3,IF(C3="S",E3-F3))*100 . Have tried many combination's of the IF function but am now starting to think this is not quite the right formula for this situation. Attached is a sample of what I am trying to achieve. The formula would need to incorporate any/all variations in currency pairs as listed in the attached sample.