NOT All Numbers Affected By Numbering Formatting
Jan 30, 2014
I'm viewing some financial data that needs formatting. I select the appropriate cells and format the corresponding values accordingly:
Right click / Format Cells / Number -> Decimal places:2 -> (check) Use 1000 Separator(,)
It's easier to read the sums and averages (at the bottom-right of the excel window) when the above described formatting is used.
But is it possible, and easily do-able, to have the zeroes show as 0 and not 0.00?
View 8 Replies
ADVERTISEMENT
Mar 11, 2008
I would like to ask for some assistance to my Spreadsheet problem.
To illustrate my problem please see below:
1------------> Level 1
1.1----------> Level 1 Sublevel 1
1.1.1--------> Level 1 Sublevel 1 Item 1
1.1.2--------> Level 1 Sublevel 1 Item 2
1.2----------> Level 1 Sublevel 2
1.2.1--------> Level 1 Sublevel 2 Item 1
1.2.2
2
2.1
2.1.1
2.1.1
2.2
and so on
I need to have a formula to automate the adding of outline numbers from "Level 1, Sublevel 1, Item 1..." So that if I deleted or added a row, the rest will automatically update themselves.
I will be the one manually inputing the Top Level and Sublevels I just need that the Items will be automatically added and computed basing from the previous level.
What I am planning to do is:
1. Check previous cell if the format has two decimal points or periods --> kinda stuck here
2. If there's only one then it will be the Sublevel 1 Item 1 (or depending to its location)
3. If there's already two periods "." then it will compute automatically for what item it is already.
ie:
1
1.1
1.1.1
1.1.2----->Checks the previous cell if it has 2 decimal places then if it does then it will add 1
View 9 Replies
View Related
Feb 20, 2008
There are various references and links to " mortgage calculators;" though they are specific to the US dollar. Is the formula still the same, irrespective of the currency and why does it come across as quite a complex calculation? i have been taksed with designing a "calculator" and don't seem to know where to start as the currency issue is confusing me.
View 7 Replies
View Related
May 12, 2013
I have a combo box where the selected abbreviation will affect the number of decimal places in another cell where figures are manually entered. The decimal places will range from 0 to 5.
The combobox contains abbreviations of product codes i.e TM, FGGT, etc up to 40+ & the range of value can vary from 4 digits to possibly 10.
So what I am looking for is if I select TM another four columns require digits to be manually input with 2 decimal places, however, if I select FGGT the same columns in a different row will need to be 4 decimal places as per the product code selection from the combobox.
e.g TM = 2 decimals
FGGT = 4 decimals,
View 1 Replies
View Related
Apr 21, 2014
I have an Excel sheet where users enter shift start and finish times (normal Excel time format) - for example: A1 might be 18:00 and A2 might be 06:00 for an overnight shift from 6pm to 6am.
All I need to calculate from these times are the number of hours to which an allowance applies, under the following conditions.
The allowance is paid for all hours worked between 18:00 and 06:00.After 10 hours, a shift becomes overtime and no shift allowance is paid.Shifts are regularly worked overnight (i.e. past midnight into the following morning)
Example scenarios include:03:00 - 15:00 would pay 3 hours of shift allowance09:00 - 21:00 would pay 1 hour of the allowance (as the shift becomes overtime from 19:00)03:00 - 21:00 would pay 3 hours19:00 to 08:00 would pay 10 hours (as the shift becomes overtime from 05:00 the following morning)22:00 to 10:00 would pay 8 hours
And so on.
I feel like I’ve got most of the pieces of the jigsaw, but I can’t put them together! I’ve got the following formulae working out bits of what I think I need:
[Code] ........
The above works out the number of hours before 6am and after 6pm respectively (which I can then SUM), and I believe also accounts for overnight shifts. This obviously doesn't include the more-than-10-hours criteria yet.
The larger formula now looks like this:
[Code] ......
…but this still doesn’t work properly! The sections referring to 1.25 were my attempt to get the shift allowance to stop if the past-midnight shift continues past 06:00, but I don't think it works properly.
I also know I’m probably using *24 and /24 more than I need to, but that’s partly so I’ve got a better grasp of what the formula is doing.
Once this is working, I'm happy using an IF… >10 formula to prevent the total number of hours of shift allowance being more than 10. However, I’m really struggling to find one single formula that will factor in shifts that might start before 6am and finish after 6pm (i.e. 05:00 – 19:00, which should pay one hour), shifts that might go past midnight and possibly past 06:00 the next day, and so on.
Lastly – not to try and complicate things further – there is an optional cell elsewhere, say A3, where a user enters ‘Y’ if the individual takes an unpaid 30 minute break at some point during the shift. This is to be deducted from whatever type of hours are being paid at the end of the shift. For example, if a shift is from 01:00 - 11:00 with the break, it would pay 5 hours with a shift allowance and 4.5 without. If the shift was 13:00 - 23:00, it would pay 5 without the allowance and 4.5 with the allowance. Is there a practical way of doing this, or does this become much more complicated?
View 1 Replies
View Related
Jan 11, 2014
I am trying to build a complex equation that has multiple parameters filled by several choices from several different dropdown menus.
For instance:
I'm building a set of 5 dropdown menus, that each lead to filling certain boxes with a number created from a formula. I've provided a small sample to explain what I'm trying to do.
My problem is, I'm unsure of how to make it so certain drop down menus affect the information inside of the next. Like, I want to have someone select "Oscar" under the characters, and have the "Lances" list come up in the Weapons column menu. I don't want the Weapons menu to be selectable in any way unless a "Character" has been selected.
View 4 Replies
View Related
Feb 20, 2013
I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.
e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..
I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same
How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?
View 3 Replies
View Related
May 21, 2014
I am wondering if I am able to display dates as text. Example: If I have a date that displays as 5/6/2014 is there anyway I can get it to display as 562014 as text?
View 6 Replies
View Related
Jul 18, 2007
We have a list of numbers in a spreadsheet (telephone numbers). The numbers have been put in with () around the country code and hypens separating the rest of the numbers - for example (44) 0111-123456
Is it possible to remove the formatting to just read as a full number e.g 0111123456? I have loads to do and the thought of doing it manually is painful!
View 10 Replies
View Related
Apr 7, 2007
is there a way to have the number formatting in Text() variable? For instance, I want to reference a cell that can change between $1.00 and $10,000,000.00.
Let's say that in A3, the number turns out to be $10,000.00.
When I put in text(a3,"$0.00") it spits out $10000.00. But if I go the other route and go text(a3,"$000,000,000) it displays "$000,010,000.00".
View 9 Replies
View Related
Jul 20, 2007
I create many charts with a lot of parantheses. I have to use Word tables usually so that I can align the parantheses OUTSIDE the numbers, so that the numbers are still right aligned. Is there a format option in Excel that would allow me to do the same?
View 9 Replies
View Related
Jun 30, 2014
I have a simple macro which extracts EUR rates from a website but the number format as appears in Excel is a disaster.
I am attaching the file : Querry EUR Rates.xlsm
How to format the rates accordingly.
Also posted here: [URL] .....
View 1 Replies
View Related
Apr 26, 2014
I am using the following format code for the y axis of a line chart. I am shortening the axis to show 3M or 500K instead of $3,000,000 or $500,000. I can't get it to work with negative numbers, I get the full $3,000,000. Somewhere I read you can only do 3 formats in a formula. Is there a way to include negative numbers using this formatting?
[>999999]$#,,”M”;[>999]$#,”K”;$#
View 7 Replies
View Related
Apr 2, 2014
I am currently using the following format to display numbers in my excel.
_(* #,###,###_);_(* (#,###,###);_(* "-"_);@
The brackets and underscores are used so that the positive and negative numbers align with overhanging brackets.
I want to modify the format such that it is able to display decimals where ever applicable.
For example
1,000 display as 1,000
0 display as a dash "-"
1.265 display as 1.265
-0.51 display as (0.51)
I tried changing it to:
_(* #,###,###.###_);_(* (#,###,###.###);_(* "-"_);@
However it added a "." to all positive and negative numbers regardless of whether there were decimals after it.
e.g.
10 displayed as 10.
-30 displayed as (30.)
In otherwords - I am trying to find the "general" format and modify it to include brackets for negative number, and also modify it so that the positive numbers aligning with the negative numbers with the ) over hanging.
View 4 Replies
View Related
May 5, 2014
I would like to enter 2 numbers in the same cell i.e;
300-900
and have it format to
3:00-9:00
I can use the following for single number entries
[Code] ......
i.e. 300 would format to 3:00
View 5 Replies
View Related
Oct 23, 2013
I have almost 300-400 records of such data which needs formatting. There are n number of IDs in the first column & corresponding data in columns 2 & 3.
For those rows which have identical ID numbers, I need to fill those rows with some unique shade of color. If you look at the next sheet, I have manually colored the rows & that is my desired outcome. The shade of color does not matter. It just needs to be unique for each ID.
View 2 Replies
View Related
Jun 26, 2013
I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levelsStatementSchool Action PlusSchool ActionMonitorIn year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.
I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula
What I'm trying to achieve
ExcelHelp.jpg
My Options sheet
Excelhelp2.jpg
View 3 Replies
View Related
Jan 27, 2009
I found out about conditional formatting for five different letters or letter combinations, using VBA, using the code below.
I was wondering if this is also possible with letters or letter combinations, BV, RV, SV, CV, Z and numbers, somewhere between 0 and 9,5.
View 12 Replies
View Related
Apr 20, 2009
I currently have account numbers formatted 1.234.5678 and need to convert them all to 1234.5678. how this can be done without manually keying 22,000 account numbers?
View 3 Replies
View Related
May 5, 2009
I have a sheet that has conditional formatting that looks at column D2 for a number and divides that by half. it then colors in a corresponding cell on a row in the sheet that matches that number. the problem i am having is with odd numbers like 23 it gives a result of 11.5 . all my cell numbers are all rounded to whole numbers. i need to round the 11.5 to 12 or 13 it does not matter which one... i just need it to round it up or down. I have tried everything i can think of but have had no luck. I attached my workbook so you can see how it works.
View 4 Replies
View Related
Apr 11, 2013
I have a table of data in which a lot of data appears as " "999" Then 'fix this, not working, formats numbers with decimals as whole numbers
c.NumberFormat = "#,##0 "
End If
' now format to line up right justified
[Code].....
View 3 Replies
View Related
May 15, 2013
I need to format a column of numbers so that it will sort alphanumerically.
All the numbers have 4 digits, and some of them have an extra letter on the end eg A B C
How can I custom format the column so that I get the numbers in order and the letters in sequence with them?
View 9 Replies
View Related
Apr 23, 2014
I have a number in column "E" T13245v1
I have a formula in column "D" =RIGHT(E1459, 1)
Which gives me "1" in column "D" (last number in string)
In column "P" I have a number which I want to apply conditional formatting to, to highlight if the numbers in "D" and "P" do not match.
What is happening is that the conditional format is seeing the formula not the number and therefore always applies the condition. How do I correct this.
View 2 Replies
View Related
Oct 20, 2006
I looked though the site with the search and saw some things that came close, but no cigar.
I have a column of numbers formated in a constant string
eg 1234121231234
The number needs to be formatted into four sections
eg 1234-12-123-1234
I have the code to put the dashes in, but I am looking for the loop statement to take it down column C reading in each number in the 1234121231234 format until the end, and replacing it one by one with the 1234-12-123-1234.
' calls the function to put the dashes in the NSN in the QRL
'First 4 of NSN
first4 = Left(NSN, 4)
'Second2 of NSN
nsn9 = Right(NSN, 9)
For Y = 1 To Len(nsn9)
second2 = Left(nsn9, 2)
Next Y
'Next3 of NSN
nsn7 = Right(NSN, 7)
For Y = 1 To Len(nsn7)
next3 = Left(nsn7, 3)
Next Y
'Last4 of NSN
last4 = Right(NSN, 4)
dash = "-"
NewItem = first4 + dash + second2 + dash + next3 + dash + last4
View 4 Replies
View Related
Feb 14, 2007
suppose I have the numbers 1-25 in a column. I want to color the numbers 1,4,7,10,13,16,19,22,25 green, color 2,5,8,11,...25 orange and color 3,6,9,12...25 red. I can not set up the conditional formatting formulas correctly.
View 2 Replies
View Related
Jun 27, 2014
I have column A with Different status like Open, closed, available etc. Column B have numbers. i am looking for a formula in condition formatting for column B.
If the cell value in column B is 2 or more, the same cell should be highlighted in Green.
If the cell value in column B is 1, the same cell should be highlighted in Yellow.
If the cell value in column B is 0, the same cell should be highlighted in Amber.
If the cell value in column B is less than 0, the same cell should be highlighted in Red.
If the cell value in column A is "Available", cell in column B should be highlighted in Amber.
View 5 Replies
View Related
Jan 28, 2014
I have some data referring to % that comes in the format of 1.1% for a positive number and (1.1)% for a negative number
How can i get the 1.1% to display green and the (1.1)% to display red?
View 1 Replies
View Related
Feb 19, 2014
I need the conditional formatting to make all numbers that are zero clear (i.e. no fill).
I need it to make all negative numbers to be red, however it doesn't seem to recognize "-1" as a number, and ends up highlight everything red when I say "highlight values < -1 red".
How would I do this?
View 2 Replies
View Related
Mar 6, 2013
formula I colored only if three numbers in a list.
attach file
formattazione.xlsx
View 5 Replies
View Related
May 18, 2013
I want to have Excel highlight one or two numbers/cells in one column based on the value in another cell. I am attempting to do this using Conditional Formatting.
For example, if X1 is BETWEEN A2 and A3, I want to highlight A2 AND A3. I have used various AND and IF functions to achieve this goal.
Also, if X1 is EQUAL to A2, I want A2 to be highlighted. This is easy to do with a simple formula.
But my problem is this: if X1 is equal to A2, I want ONLY A2 highlighted. If X1 is between A2 and A3, I want BOTH A2 and A3 highlighted. I cannot figure out how to achieve both. For example, when X1 = A3, A2 AND A3 are being highlighted, instead of just A3. I would prefer to do this without a macro, if possible. Can this be done?
Example formulas I have tried which easily achieve the highlighting of two cells when the value is between them:
Condition 1=IF(AND($I$35>I8,$I$35<I7),1," ")+IF($I$35<>I7,1," ")
Condition 2=IF(AND($I$35<I6,$I$35>I8),1," ")+IF($I$35<>I7,1," ")
It appears that the +IF, etc. functions are not doing anything.
This is more complicated by the fact that the value of, in the above example, I35 is based on a value from another sheet (I used a function to transfer a value from one sheet to I35 in the other sheet). As data are entered on Sheet1, the value in I35 in Sheet2 changes.
View 14 Replies
View Related
