Find Percentage IF Number Present
Feb 2, 2009
I have a spreadsheet with 6 sheets. There are 3 sheets that have information regarding the soil, geology and drift deposits of an area. The other 3 sheets relate to these sheets to display the information for each area. The way that the spreadhseet is set out pretty much explains what i am trying to archive. Basically I want each sheet to say whether or not the rock, soil or drift type is present for each area. And if it is present to calculate the percentage of total area. I have created a pivot table for each of the info spreadsheets - but need it to look like the "table" sheets, because it needs to be added to a report. Any questions and i'll try and answer them , but it's quite difficult to explain in words what i am trying to achive.
View 4 Replies
ADVERTISEMENT
Apr 5, 2014
I have a daily report that gives a percentage based on what is typed into the "Supplier" and "Total Sales" columns but I'm having trouble writing the following formulas since my excel knowledge is relatively low.
1. Where a cell in the "Supplier" column says anything but "Den", "Burrowed" or "Studio Nyx" I need the "60%" and "40%" columns filled with the relevant formula (=B11*60% for example) and the "100%" column blank.
2. Additionally, if the cell contains "Den", "Burrowed" or "Studio Nyx" I need the "100%" column filled and the "60%" and "40%" blank.
View 6 Replies
View Related
Apr 23, 2008
I have a dataset in Excel 2007 where I need to determine the number of census tracts (as rows) it takes for each column of data values (populations of different ethnic groups) to reach a cumulative percentage of 50% of their City group total. This is a measure of concentration that will vary from group to group.
I can do this manually by sorting each column from largest population percentage to smallest population percentage, then shift-click selecting the first row, scrolling down until I see the sum of percentages shown in the bottom right corner first reaches 50.000% or more.
Is there a cell formula I can use to determine the number of rows it takes to meet the criteria? I have to do this for 250 columns.
Is there a cell formula I can use to determine the number of rows it takes for a column of data values to reach a specific amount, that is first reach a cumulative percentage of 50.000% of the column total? It must be based on the data values from largest to smallest but I don't want to have to manually sort each column I have.
View 14 Replies
View Related
Sep 11, 2009
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
View 2 Replies
View Related
Jun 5, 2006
I am looking for a VBA Code which will:
1. Search for a specific File.
The file will the same file (ABC.xls) BUT the user might want to change it by typing a new name & Extension in the first Inputbox.
2. A second InputBox (Drive & Path) should now Pop-up.
If it will be left empty - the search should take place throughout ALL the installed HDs.
If the user decides to minimize the search range he/she will type a Drive & Path, the search should reduce/constrain itself to the specific Path ONLY.
My task is to show its Path location in a MsgBox (or to show "file not found")
It might be a good idea if the code can be extended beyond presenting the Path to:
1. Open the Windows "Explorer" (or "My Computer") in the specific Path with the file being selected (marked).
2. Opening that file, in "Excel", upon finding it.
A 3 section, VBA, code will be more than appreciated.
View 9 Replies
View Related
May 14, 2012
So I am trying to write a bit of code that will insert a number in sequence in column A if there is data in column B. I need the column to be numbered 1-### if column B contains any data. I not been able to find this code anywhere, just delete if or count if statements that do not give me what I need. I think I have a few pieces of the code (e.g. Active Cell Offset) but can not seem to fit together in the correct order.
View 3 Replies
View Related
Jan 6, 2014
i am trying to find the time difference between two cells and present the date in a third cell. The data in the cells are in a non standard date/time and i need to create a special format i think. The cells look like this.
fldcollected fldaccepted Type Time between being received by database and eccepted
2013-11-06 15:59:29.1002013-11-07 08:41:12.000PSTN
View 3 Replies
View Related
Feb 12, 2014
I need a formula to give me a MIN or MAX value only if there is a number greater than 0 in the cell AND not alphabetical letters (as some cells do contain). I need it to return the value for that cell across about ten sheets--I have that part figured out (I think). But I keep getting a #Ref error.
View 9 Replies
View Related
Aug 25, 2009
I have a formula that I use to tally data and return the number of times a particular value is present in column C.
IF(COUNTIF($C$2:C2,C2)=1,SUMIF(C:C,C2,E:E),"")
I copy this formula down the sheet until arriving at the last input in column "C".
Can someone tell me how to write this in code. All I would like visible in Column "F" is the output not the formula.
View 9 Replies
View Related
Apr 27, 2012
How do I work out the parts scanned value from a percentage. Example below.
Overall99.75%Total Stock 27194Parts scanned??
View 2 Replies
View Related
Feb 16, 2010
Column A contains multiple data, work order, account number, account name such as "#12345 555 hello"
I have a list of account numbers and want to see if or not these account numbers are listed in column B.
I tried =IF(ISERROR(SEARCH("555",a1)),"","Yes") and it did work for the one cell, but how can I change the search cell from H30 to an entire row? Also, is there a way to reference column B that has the list of account numbers instead of having to manually type in the 555? So, have it say something like this:
=IF(ISERROR(SEARCH(B1,A1:A30)),"","Yes")
View 9 Replies
View Related
Dec 17, 2013
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total
1234Bed150
5678Bed100
1547mattress52
58658mattress188
12356bed12
8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
View 2 Replies
View Related
May 27, 2014
I have a series of numbers in cells B2 to G2. The maximum value of each of these columns is found in B5 to G5. Is there a formula that will calculate the percentage of each of the values in row 2 (B2 to G2) and tell me which is the lowest number based on the percentage? Example...
Row 2: 12, 100, 43, 1444, 141, 332
Row 5: 25, 140, 55, 3000, 244, 440
Which number in row 2, is the lowest percentage of the total possible points found in row 5?
B2 is 45% (12 divided by 25), C2 is 71%, D2 is 78%, E2 is 48%, F2 is 58%, G2 is 75%.
I want a formula to tell me in a new cell that "12" (in B2) is the lowest percentage in that series.
View 4 Replies
View Related
Mar 4, 2014
I have totals in d18 and e18 and need to find the percentage increase or decrease between the 2. E18 is last year's value and D18 is this year's value. Which formula will give me what I am looking for:
(D18-E18)/D18 or (D18-E18)/E18
View 2 Replies
View Related
Feb 21, 2013
I am trying to figure out why my number will not format correctly... I have a number thats value is pulled in via external data that gets updated everyday.
Everything works fine there, but I needed the format of that number that was pulled in to have parenthesis around it...
I know that one can add the parenthesis to the number by using the ="("&(Number)&")" function...
What the problem is is that when I add the parenthesis to the number it will not allow me to view it as a percentage; it will leave it as a full general number (Ex. (0.0006), instead of (0.06%)
Is there a way to get the percentage number format back?
View 2 Replies
View Related
Oct 16, 2012
Is there a way to add a percentage to a number, I know the Method of Multiply by 1.25 for 25% The problem is there another way to formulate without the "1."
Sheet1 LMN
32640%15.28?
View 2 Replies
View Related
Jun 26, 2006
Is there any way to make a cell display both a raw number and a parenthetical representing a percentage based on a formula? I know this may sound strange.
Examples:
9 (25%)
12 (39%)
I could get it done via merging or =_&_ style entries, I know, but is there any other way?
View 2 Replies
View Related
Aug 10, 2005
example 1:
This years sales are $3700, a decrease of 11.6%. What would last years
sales be?
example 2:
This years sales are $4500, an increase of 151%. What would last years
sales be?
View 12 Replies
View Related
Jul 17, 2008
I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out.
Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?
View 11 Replies
View Related
Jul 8, 2014
I am trying to create a custom number format.
Here is what I have come up with: 0.00%;[Red]-0.00%
The problem is, it has no "," it would display as 23555555.6% or -2355555.6%
I need it to display as: 23,555,555.6% or -23,555,555.6%
How would this be written?
Additionally, I have occasion to "abbreviate" in the following format:
23.55MM% or 1.5M% or -23.55MM% or -1.5M%
For currency, I have it written in this manner:
Currency: [>=1000000] $#,##0.0,," MM";[>0] $#,##0.0," M";[Red]($#,###," M")
I just freeze when trying to convert to percentage.
View 1 Replies
View Related
Oct 10, 2006
I would like to use vba to copy a value in F31 to B5 in sheets("2") and express it as a percentage.
For example, the value in F31, 102 is copied to B5 as 102. Like to have it express as 102%.
Sheets("S1").Select
Range("F31").Select
Selection.Copy
Windows("Report.xls").Activate
Sheets("2").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
View 4 Replies
View Related
Sep 7, 2007
"in Excel" - my formula for cell C11 is: "What percentage of the time (occurrences) was the value in cell B11 greater than the values in A1 thru A10?"
As in...:
100
99
98
97
96
95
94
93
92
91
90...95.....40%
89...16.......0%
88...123...100%
87...90......20%
So no data in first 10 rows of Col B. (Or Col C, of course.) Then for the value that appears in B11, how many times (out of 10, in this example for sake of ease) was that value greater than the entries in the first ten entries in Col A:
95 (cell B11) was greater than A10, A9, A8 and A7 (91, 92, 93 and 94). So 4 times out of 10, B11 was greater; 40% is the answer the formula would yield.
16 was never greater than any entries in A2 thru A11, so 0%.
And 123 was greater than all the entries in A3 thru A12, so 100%. Etc, copied down the sheet...
View 5 Replies
View Related
Sep 8, 2007
Spreedsheet on on sales and taxes
Need formula for cells.
3 different cells involved... B1 will have $ amounts, B10 tax code, B25 will have total of tax applied due to code.
Exp; cell B10 will contain the tax code... if you enter..... "1" in the cell the calculation will be 7% in cell B25, enter "2" in cell B10 and the calculation will be 6% in cell B25 , and if you enter "3" in cell B10 the calculation will be 13% in cell B25.
View 6 Replies
View Related
Jun 5, 2008
I know for some of you this will be pretty simple but im having trouble figuring it out. Attached is a shortened version of what i am trying to do. I want the Percent Attendance column to represent the cumulative percentage(hope I used the correct phrase). So for Person 1, it should currently say 100%, person 2 it should say 66.67% and so on. There are 5 days that i want to get the percentage, but because we haven't gotten to 2 of them yet, using a regular sum formula for the entire five days gives me the wrong values.
View 2 Replies
View Related
May 15, 2014
On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:
Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)
The allocation percentage is based on the below; anything outside of this should result in "Other".
Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%
Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%
Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%
Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%
View 1 Replies
View Related
May 15, 2014
I have data one sheet which is
Date Ref No Type %complete
Monday 1 Erect 75
Monday 2 Erect 50
Tuesday 1 Erect 95
Wednesday 1 Erect 100
On the next tab I am consolidating all the reference numbers and want to capture the latest % complete
I have been trying =Max(if ref no=1 and type = Erect, % complete),1
But when I try it for reference number 2, it just says 100% aswell, when that should be 50%.
View 1 Replies
View Related
Mar 28, 2012
I am trying to create a formula to calculate the total number of calls that I need to receive to hit the target % if the current % is less than the target %. I have tried various formulae, but not having much luck. Also, it shouldn't show me any negative figures.
A = Departments
B = Calls Dropped
C = Calls Received
D = Current % (C/F)
E = Target %
F = Total (B + C)
G = Required (When I enter B, C and E, the rest needs to calculated automatically. G should tell me the amount of calls I need to receive in order to achieve the target specified in E)
Departments
Calls DroppedCalls ReceivedCurrent %Target %TotalCalls Required to hit TargetDept 1
21083.33%95.00%12 Dept 2
067100.00%90.00%67 Total
27791.67%95.00%79
View 7 Replies
View Related
May 14, 2014
I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
View 5 Replies
View Related
Aug 20, 2013
I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.
I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.
View 9 Replies
View Related
Mar 1, 2012
Items in Column A1 are calculated by (B2/4+5)*1.4 Items located under the columns 2000, 3000, 4000, etc... 10,000 are calculated by taking the top number, eg 2000/(A1 cell value)+the column B number. 2000/7+0 = 286 (rounded numbers)
I need to find an way to look up for x number (2000,3000,4000, etc...) find the smallest number in that column and then return the value in column A1.
Cell
A1 Number >2000300040005000600070008000900010000
70 2864295717148571000114312861429
84 24236148059971883795610751194
[Code].....
View 2 Replies
View Related
