Using Displayed Values Only
Nov 9, 2009
After searching this board, I am using a check for this issue to alert when the problem occurs, but I'm hoping to tell Excel to fix the problem itself. The issue occurs when a set of data equals 100%, but when fewer decimals are displayed, the displayed total equals 101% or 99%.
Example:
0.6%
1.6%
97.8%
(Now when the display is changed to no decimal points)
1%
2%
98%
As you can see, the data appears to total 101%, and my boss is being a (insert any comment you probably have about your boss) about me not manually reviewing every single report that goes out for this 1% difference. As there are probably 400 different points at which this situation can occur on a daily basis, working harder is not the solution. /rant
I thought I once heard about a formula or setting where Excel will only calculate the displayed values instead of the actual value or formula. Ideally, the formula would know to take the three values, figure out the 100% limitation, and round the largest one down so the other two can round up. The example above is probably 99% applicable, as we would be rounding the largest value down in order to round the other values up.
Meaning intended result:
1%
2%
97%
View 9 Replies
ADVERTISEMENT
Apr 24, 2014
I have a pivot table that summarises data from a timesheet. The summary looks something like the following and shows the number of 15 minutes intervals spent on a range of categories and jobs:
PivotTableSample.PNG
How can I have this pivot table display the number of minutes instead of the number of intervals (i.e. it needs to take the value displayed currently and multiple it by 15). I'm sure there must be some simple way to achieve this but can't figure it out (I don't want to use vba).
View 4 Replies
View Related
Dec 23, 2013
Is it possible to dynamically change how values are displayed in charts?
I have a list which references different named ranges. It either needs to display the values in a graph as a number (without decimals) or a percentage depending on the source. Is this possible? Maybe with some VBA? Or am I asking too much from excel?
View 4 Replies
View Related
Oct 11, 2011
I'm currently working on a test matrix that has high level and mid level test cases. The High level results cell is tracking all mid level test cases below it. If all mid level test cases are displayed as "Pass" then the High level test cases displays "PASS". If any of the mid level test cases is not displayed as "PASS" then the High level test case result is displayed as "FAIL" However, "PASS" and "FAIL" do not accurately represent the status of the test cases in the instance of them not being filled out. Mid Level test cases start off in the "Select Result" state which basically says it that test has not been performed. I would like to have a third value displayed as "NOT COMPLETED" or "IN PROGRESS". Below is the current formula that I am using.
=IF(AND(C50="Pass",C51="Pass", C52="Pass", C53="Pass", C54="Pass", C55="Pass", C56="Pass"),"PASS","FAIL")
On thing to keep in mind that may or may not make a difference is that one of the High level test cases has 30 mid level test cases and from my understanding, IF / AND function only allows up to 30 logic inputs (not sure if that is the correct terminology or correct at all).
View 8 Replies
View Related
Jul 14, 2006
I've got the following formula in a column: =IF(C10/B10>=1,20,IF(ISERR(C10/B10),0,0)). The purpose is to ensure that only one of two values (20 or 0) is displayed, regardless of circumstances that regularly generate the # DIV/0 error. The if(iserr(...)) part works fine when tested on its own, but when nested, it's still allowing the error to display.
View 3 Replies
View Related
May 24, 2012
I have the following data on a spreadsheet created by a formula:
A1: 1.1234
A2. 2.234
A3: 3.34
I have the data formatted to only have two decimal places so it looks like this:
A1: 1.12
A2: 2.23
A3: 3.34
I'm using the following formula in cell B1 to display the values of those cells in a single cell in order A1,A2,A3:
=A1 & "," & A2 & "," & A3
However when the values of A1, A2 and A3 appear in the in cell B1 they appear as: 1.1234,2.234,3.34
Is there a way I can edit the formula or format something so the values in B1 appear as with only two decimal places? i.e 1.12,2.23,3.34
View 3 Replies
View Related
Nov 27, 2011
I've data from A1 till G1. Now I will hide column B & D I just want to have total of displayed columns that must ignore values written in column B & D
View 1 Replies
View Related
Jun 18, 2009
I have a worksheet I use at work that calculates volume proccesed hourly, and also shows a running total as you enter hourly figures into the table, my question is can you hide everything in the result cell until the figures for each hour are entered ? currently all the result cells have irrelevant numbers in them until you enter the hours figure and the formulas do there thing, I could relly do with them being blank until the relevant hours information is input?
View 2 Replies
View Related
May 14, 2013
I want no time functionality nor any calculation of the entries. I simply want to type into a cell for example: 0908 and have it displayed as 09:08. I tried custom formatting the cell to hh:mm but then it displays as 00:00.
This is for a real-time incident logging sheet and the less the operator has to type, the better. I know it is only an extra Shift+: but it will be so much easier without having to do it.
View 6 Replies
View Related
Jun 11, 2009
I like the way the names auto fill. Though would ilke to have it so say when roc is entered it shows as a dropdown so you can see that there are more than one ROCHESTER.
Like a search i guess, That would be valid to the first to letters. so if you had name donald, david & daffy when D is entered it showes all three then when DA is showes only David & daffy.
The project that i will 1st use this on is a contact address ph book.
View 14 Replies
View Related
Jul 7, 2009
I am just wondering if there is a way to allow a macro to continue running in the background once a userform has been displayed? I have disapled the 'close' button and the last action of the macro is to re-enable it.
View 10 Replies
View Related
Jan 3, 2012
I want to sum cell A1 and result will be displayed in B1
AB1ValueResult29292.50*10+8952*15+8550*10
View 9 Replies
View Related
Feb 20, 2012
Using the below code, I want data in range with format (for ex: J2:K7) to be displayed in the email sent in the place of Email_Body1 place.
Private Sub CommandButton1_Click()
Range("H2").Value = Date
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String
Dim i As Long
[Code]....
View 9 Replies
View Related
May 29, 2012
I'm creating a customized, interactive calendar with several drop-down menus included for each day of the week. I want to have a self-adjusting section at the bottom of each month that shows which values have not been selected in the drop-down menus on any day.
I have a column next to the source list for the drop-down menus with =IF(ISNA(MATCH formulas to show if a value has been selected anywhere on the calendar. If no one has selected a certain value from any of the drop-down menus then "XXXXX" shows up in the row next to its source (one "X" for each column, Mon-Fri). If a value has been selected, then it says "FALSE".
I now want to have a section that lists only the values that have not been used, with "XXXXX" next to the source data. However, any formula I use is not able to differentiate between "FALSE" and "XXXXX" because they are both "0" values in a numerical sense. Can I create a formula that references the displayed value, rather than its true numberic value? What formula would be best to use to list only data not being selected from the drop-down menus? Is there a simpler way of displaying this information than what I'm trying to do?
View 9 Replies
View Related
Nov 21, 2007
I have a column with dates in the following format: dd/mm/yyyy h:mm
but im doing an XML export and excel only exports in text format. I would like to somehow resterize or only use the displayed amount instead of the text which is exported (ie- 37805.1640277778)
View 9 Replies
View Related
Jul 17, 2008
I have a macro that once clicked shows my userform. Is it possible to have this userform displayed whilst also allowing the user to click into other cells on the worksheet?
View 9 Replies
View Related
Jul 10, 2006
On my worksheet, cell A1 and A2 both contain the value:
0.1655555
I've changed the number formatting to three decimal places so it appears on the sheet as 0.166.
In cell A3 I have a forumla =A1+A2 which currently comes back with the value 0.331 (0.1655555+0.1655555=0.331).
Instead of adding together 0.1655555 I want it to add the displayed value of 0.166 so it should then come back with the value 0.332.
View 3 Replies
View Related
May 29, 2007
While working within the same worksheet, why not put all the routines in one Module instead of using different modules?
BTW, what made me raise the above question is that I have 2 modules in my test worksheet. But when I go to Tools | Macro | Macros > This Workbook, it does NOT show the sub-routines of the first module
View 5 Replies
View Related
Jul 27, 2014
I have created several records, each record is in it's own row. I'd like for the row to be displayed if the name of that row is selected in a drop down. How can this be accomplished? I've attached the spreadsheet to show my current progress. When a player is selected in a drop down, I would like for that players record to be displayed below the drop down.
UFC.xlsx
View 4 Replies
View Related
Dec 16, 2013
I need to display a subtotal amount in a field named Invoice Total in my spreadsheet. I created a subtotal, which is the amount that I need on each line in the invoice total column. Here is an example:
Invoice Number, Invoice Total, Line_Item_Total
99223, 0, 8.77
99223, 0, 8.77
99223, 0, 17.55
99223, 0, 8.77
subtotal: 43.86
99527, 0, 8.77
subtotal: 8.77
View 4 Replies
View Related
Apr 20, 2014
I have the need to create a workbook that will return values based on the entering of an abbreviation. I will use the chemical elements as an example, I wish to have a column formatted so that if 'Au' is entered the cell populates with 'Gold' or if 'Pb' is entered then the cell is populated with 'Lead'
So in my case I have all the abbreviations in one column and all the full descriptions in another (approx 500 different codes/description)
View 3 Replies
View Related
Sep 21, 2009
I am trying to have a cell in a diffrent work sheet display information from another sheet.
I entered ='Weapons Table'!A48 and it is displaying in text not what is actually in that cell.
on the cell above it I have entered ='Weapons Table'!A47 and it displays fine.
I've tried typing = then just clicking the cell, and it is still just displaying it as text. I've spent 3 days on this sheet and have only 1 error, that is driving me crazy.
View 11 Replies
View Related
Apr 2, 2013
I created a drop down list in three cells for date, month and year using data validation.
But I need these cells to display by default DD,MM,YYYY.
what's the way to do it.
View 1 Replies
View Related
Jul 15, 2008
I want to be able to record the highest value displayed in a cell (b2) in another cell (c2). Cell b2 is a cell that has continuously updating numbers from a live feed, which are updated approximately every second.
--I'm not sure if I need to have a log created based on the changing numbers. If so, I wouldn't anticipate the length of time to determine the highest value would be more than 7 hours.
--Is there a function/filter that would be able to report the highest number recorded?
View 10 Replies
View Related
Jul 10, 2009
I have a program with a line that deletes a worksheet like so: Sheets(Output).Delete. Where "Output" is the name of the sheet (a string). This works fine, but everytime this line is excecuted excel pops up a message box "Data may exist is the sheet(s) selected for deletion. To permanently delete the data, press Delete." and I have to click delete. Is there anyway to stop this message being displayed?
View 2 Replies
View Related
Aug 5, 2009
I am currently having a very painful problem with dates.
Currently I assign a value which happens to be a date to a string.
View 11 Replies
View Related
Jul 6, 2006
I have an expense sheet calculated, need to show the sum of the "+" number in
a cell that does a subtraction of 2 numbers .. income and subsidized ... as a
- that it negitave amount in red if it is less than a specific numbe. ie 2500
View 16 Replies
View Related
Aug 20, 2008
I am using index/match to dynamically populate a table in 'sheet2' based on data in 'sheet 1' The index/match in the cells is:
=IF(IF(ISNA(INDEX(Attend,MATCH($A4&C$2,ID_2,0),6)),"",(INDEX(Attend,MATCH($A4&C$2,ID_2,0),6)))=0,"",(IF(ISNA(INDEX(Attend,MATCH($A4&C$2,ID_2,0),6)),"",(INDEX(Attend,MATCH($A4&C$2,ID_2,0),6)))))
Attend = Named Range
ID_2 = Named Range
The index/match will return a value for each cell in the table (from column 6 in this instance); the values are either numbers (1-6) or text (or a blank if there are no matches).
What I would like to display in the comments box is the value of the adjacent cell in sheet 1 (ie index/match from col 7) when the match was a number between 1-6.
I've been struggling with VBA for an index/match based on cell offset, triggered by a worksheetchange? that is then used with .clearcomment and .addcomment when the cell value 16 to dynamically display comments.
View 9 Replies
View Related
Jan 15, 2010
I would like the following data to be displayed in userform as a splashscreen, how can i do this? The sheet name will always be Cash....
View 9 Replies
View Related
Apr 4, 2007
i would like to work on the worksheet while msgbox is displayed. for userforms, setting modal to 0 worked fine... but i couldnt find such function in msgbox..
View 5 Replies
View Related