If Percentage Calculation Negative Show Zero
Oct 22, 2007
i am having a hard time in my microsoft excel class
for the life of me i cannot think of a formula to answer the question below
every time i type in =(c3)-25-80% i get the wrong answer
i know there has to be a different formula
Cost of visit= $113.00
Q=Your have a co-payment of $25.00 and the insurance will pay 80% of the remaining cost. Create a formula for the visit that will calculate the total amount you will have to pay.
View 4 Replies
ADVERTISEMENT
Oct 7, 2008
I am a Safety representative and on a monthly basis I have to check for seat belt usage. I can not, for the life of me, figure this out.
I have attached an example. In the example out of 22 vehicles there were 2 individuals not wearing seat belts. So cell J4 should be 91%
I can calculate % not wearing with [=sum(D4:E4,H4:I4)/SUM(B4:I4)]
but I want to show % wearing.
View 2 Replies
View Related
Feb 17, 2009
i have for example column I, J & K. col I and J has both negative & positive values and K (I/J) works out the %
Col I Col J col K
135975 583333 23%
-27748 583333 -5%
-18186 583333 -3%
272550 583333 65%
But i need a formula or formatting that always shows the negative percentage as a zero.
View 2 Replies
View Related
Dec 13, 2007
I have a calculation in which the result is a negative number. Is there a way to return a zero if the result is negative?
View 13 Replies
View Related
Apr 21, 2008
i have a sheet which adds up stock = SUM(F6-D6) (told u it was basic) what i want to do is if a negative number is the answer i want the sheet to change it to zero.
View 3 Replies
View Related
Dec 15, 2008
How can i do this using Excel 2007. I have to cells, A1 and B1. A1 is always Positive whereas B1 might be Positive or Negative. I need a calculation/formula in C1 that ADDS cells B1 and A1 (B1+A1) IF B1 is Negative and SUBTRACTS A1 from B1 (A1-B1) IF B1 was Positive.
View 3 Replies
View Related
Jun 11, 2013
In the Excel sheet i have 2 tabs
1st tab name :Percentage
2nd tab name :Defects.
Need VBA Code for the below scenario :
In the Defects sheet it should perform the autofilter by selecting the Criteria as Severity as Critical and Status as Closed and finally it should take the count of visible cells and it should populate value in the sheet1 (Percentage sheet) in the Critical defects closed field .
Again it should perform the autofilter by selecting the criteria as severity as Critical and status should (New,open ,reopen,retest ,pending closure)and finallu it should take the count of visible cells and it should populate value in the percentage sheet in the Unresolved defects field.
Now percentage calculation should be performed between the Critical Defects closed and Unresolved Defects.
Here is the formula= Critical defects closedUnresolved Defects * 100.
How to perform this Scenario through VBA Macros.
Please find the attached sheet : sample.xls
View 1 Replies
View Related
Apr 16, 2014
I am trying to get my variance percentage to calculate correctly but I am struggling when it comes to one of the value being zero. It works fine for the below example when both the variance and last year figures a zero.
Last Year This Year Variance Percetage
0 0 0 0.00% IF(Last Year=0, "",Variance/Last Year)
0 100 100 0.00% This should be 100%
View 6 Replies
View Related
Jul 2, 2013
In the attached table the fees generated are on a sliding scale and the total is the fee generated within these ranges
I would like a formula in b10 to give me the value based on the total sale figure, i.e. 80.00. I've searched the board and tried some very long "if" statements and "lookup" table but to no avail.
Is there a formula I could use to calculate the desired value, preferable using cell references and not the actual values.
Sale Value
% Fee
Upto 2000
1%
2001 to 3000
1.50%
[Code] ........
View 2 Replies
View Related
May 26, 2014
I want to calculate % increase with time related fields
Field A1 = 01:53 (formatted custom as mm:ss) happy to format some other way
Field A2 = 02:08 as above
I want to know the % increase. The correct answer is 13.27% , as 01:53 = 113 seconds and 02:08 is 128 seconds and 128/113 = 1.1327 so 13.27%
View 6 Replies
View Related
Aug 21, 2013
This is so simple, yet Excel doesn't give the correct value. So, here you go, I only have 2 Cells and 2 Labels. One Label for one Number and the other Label for the other Number.
For the Pie Chart I chose Cells:
Cell B36 with the number $54288
Cell B39 with the number $166113
By simply viewing this one can see one of the Percentages should be around 33% and the other 66%. However, Excel gives it a Percentage of 25% and 75%.
How can I get the correct values?
View 2 Replies
View Related
Jul 1, 2008
I have a column of numbers such as
1001150
1001124
2224445
I need add a period in the following locations
10011.50
10011.24
22244.45
I figured this out using a format rule of
#.##
I then need to make the numbers negative so I did
-#.##
but this doesn't "stick", if I filter the numbers by negative numbers, none of them show up. So how do I make the formatting actually become the numbers? Auto Merged Post Until 24 Hrs Passes;After doing some more research I found the "precision as displayed" option. I can't find this option on Excel 2007, but I moved the files into 2003 and the option doesn't do anything. It is not permanently changing the column that I have added the formatting too.
View 4 Replies
View Related
Jul 3, 2014
I have a percentage calculation that I need to ensure excel does not round up the last digit.
My calculation is 2463000(cell:I13) divided by 257000(cell: I14+I15)
Excel calculates this value to be 0.958365759
However I need it to display and re-use only 0.9583657 as my calculation has to be precise to 7 decimal places.
If I use the formula =ROUND(I13/SUM(I14+I15),7) then excel returns the value 0.9583658 - it rounds the last digit up.
I have tried to use the option "Set precision as displayed" and set my decimal places to 7 but this still sees my 7th digit rounded up.
How do I set it so the 7th digit is not rounded up.
My original formula is =IF(AND((L3+L8+L13)<=O3,(L3+L8+L13+L18)>O3),((O3-(L3+L8+L13))*I18/L18))
I have updated it to be =IF(AND((L3+L8+L13)<=O3,(L3+L8+L13+L18)>O3),((O3-(L3+L8+L13))*(ROUND(I18/L18,7))))
View 9 Replies
View Related
Oct 6, 2009
I am building a conversion calculator on a userform. There is a textbox I've named "UserPctOff" where the user enters their value. I've got a label that I'm using to record the converted value. I keep getting Type 13 Mismatch error.
Here is my code.
Private Sub UserPctOff_AfterUpdate()
UserPctOff = Format(UserPctOff.Value / 100, " Percent")
LabelMDRate = UserPctOff.Value / (1 - UserPctOff.Value)
End Sub
View 4 Replies
View Related
Oct 12, 2008
fix my (if formula) so it includes data from sheet 2. Sunday and monday values change but every other remains the same. I was given this formula and cant seem to add to it. Also is there a way to show only what i need instead of negative and 0 balances?
View 2 Replies
View Related
Jan 19, 2010
When a user enters an amount in a cell, in £'s, i need it to show a negative amount. So if they enter £100 I want excel to regard it as -£100.
View 4 Replies
View Related
Jul 20, 2006
I have a spreadsheet that shows when individuals completed different events. The events are on top and the names are down the side. There are dates in the middle. I would like to know if it's possible to show a percentage of dates that are current- so take the number of fields with current dates and divide them by total number of fields. I would also like to find a percentag of events complete by each individual.
View 5 Replies
View Related
Nov 4, 2012
I have devised a simple formula when a member of staff enters a date and time into a cell it starts deducting from todays date and time. ie:
Cell A1 - Date and time is entered by staff
Cell B1 - Is the above cell +24hrs
Cell C1 - NOW()
Cell D1 - B1-C1 Formatted in hours ( [h] :mm:ss (Like a count down clock)
The problem i have is that i cannot get Cell D1 to show minus ours it just goes to negative and shows continous #####. Is there any way i can get Cell D1 to show the hours it has gone minus by.
View 6 Replies
View Related
Oct 9, 2006
I have a spreadsheet where I track income (positive) and expense (negative) items and compare budget to actual amounts. I want to create a simple bar graph comparing budgeted amounts to actual amounts for each item.
However, when I do this, I see all of my income items above the line (pointing up) and all of the expense items below the line (pointing downward).
Is there an option I can select to just use absolute values for the items so they all appear above the line?
View 3 Replies
View Related
Sep 27, 2006
I have a spreadsheet which has a number of columns populated with a day number and below each day number there are some numbers. What I would like to do is to compare the values for Day1 with Day2, Day 2 with Day 3 etc. Is there a smart way to do this using VBA? I am enclosing an example to show what I would like the macro to do.
View 8 Replies
View Related
May 23, 2013
I am trying to add 2 cell values together then show the total as a % of a value in a 3rd cell, however I also need it to allow for 0 values in the chosen cells without displaying an error message or it messes up the average formula elsewhere on the sheet?
View 2 Replies
View Related
Jul 31, 2014
I would like to know how to input 10% 25% ect then in another cell the graph bar growing till 100% mark is completed.
View 6 Replies
View Related
Feb 20, 2013
I am trying to calculate a percentage complete based on what a cells color is, the function must count the cells of a certain color (signifying in this case that something is complete for a particular "area") and distinguish that from cells in the same data range that aren't colored and calculate the percentage that are complete.
View 3 Replies
View Related
Jul 18, 2007
Does anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.
View 2 Replies
View Related
Sep 15, 2007
I have a rather large report full of data, 10+ columns contain a RANKING column, and these ranks, 1-10, are assigned by a % bracket:
1-20% = 1
21-40% = 2
41-60% = 3
61-80% = 4
81-100% = 5
101-120% = 6
121-140% = 7
141-160% = 8
161-180% = 9
181-200% = 10
There are several hundred rows in each column that all need individual ranks. My first thought is to program a if then else statement for 1 to 10 with a % limit range, but this isnt feasable as i would have to program a separate if statement for each of the many hundred rows, or do one heck of a fancy loop that i just cant contemplate (as the rows are split and sepperated... no solid uniform place for the data to be plugged.) What is the best way to go about this? I'm at a total loss of ideas with my knowledge of excel.
View 2 Replies
View Related
May 28, 2014
How to show progress bar for both percentage completion and time left.
The code below is already created with percentage completion. Just to add time left.
[Code] ........
View 6 Replies
View Related
Jan 24, 2013
Looking for a formula to show percentage increase of a figure.
Cell A1 is £45,000
Cell A2 is £51,000
Cell A3 ANSWER result shown as a percentage increase.
View 6 Replies
View Related
Apr 25, 2006
List Price $46.98 (e2)
Net Cost $19.53 (e3)
How do I enter a calculation that will show me my discount percentage from my supplier? (e4)
I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item.
View 9 Replies
View Related
Nov 9, 2009
I have Column A to show the Project I am working on and Columns B-M show the months of the year with the figures under certain months. In Column N I just want to show where a figure falls under a certain month, I want Column N to show the month (header) that particular figure is under e.g. N2 should return a value of "March", N3 ro return a value "June" and N4 to return a value "April/September".
View 5 Replies
View Related
Dec 18, 2013
I have a Pivot Table with fields for months and weeks. I also have a "Show Values as % Difference Field" that shows monthly or weekly % change. When I collapse the fields so that it goes from weekly to monthly (or vice versa), I have to manually change each Show Values As % Difference column. Is there a way to do this automatically or quickly?
View 1 Replies
View Related
