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