Taking Percentage Of The Larger Of Two Cells
Sep 19, 2006
I hope my title was clear enough, but I’m not sure how to set this up. I have numbers in cells B4 and B13. I want to take the largest of these two numbers and multiply it by 0.85. I then want to take this number and compare it to a number in cell B23. If B23 is greater than the larger of B4 and B13 X 0.85, I want B33 to display “No” in a boldfaced red font. If B23 is less than the larger of B4 and B13 X 0.85, I want B33 to display “Yes” in a normal black font. This formula will be in cell B33.
View 5 Replies
ADVERTISEMENT
Nov 4, 2008
I have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero.
I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero.
View 4 Replies
View Related
May 21, 2014
How i can take just team names in A column as home and away to B-C columns ?
View 4 Replies
View Related
Aug 16, 2013
Is it possible to take text from 2 different cell and inserting into one cell?
For example:
Cell A1 reads 'John' and cell A2 reads 'Smith' can I make cell A3 read 'John Smith' by taking those two bits of information?
View 2 Replies
View Related
May 12, 2007
I've been trying to learn how to write code in VBA. I've been learning for about 2 weeks now (trying to learn it for work) and heres my problem. I have a macro that will create a pivot table from raw data that I input. From this pivot table, I want to go to the last row in the pivot table (the row that takes grand totals of each column of data). I want to write a macro that will take those values (located in a workbook called AR age sorting) and place them in cells in another workbook (called AR aging analysis). Basically cells D6-D9 in workbook "AR aging analysis" should equal the values of the last row in my pivot table in workbook "AR age sorting" (last cell with data in columns J-M). I thought this would be easy but I've run into some issues. Heres the code I use:
Sub aging_summary()
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Set Sheet1 = Sheets("AR age sorting")
Set Sheet2 = Sheets("AR aging analysis")
For n = 10 To 13
For M = 6 To 9
Sheet2.Range(Cells(M, 4), Cells(M, 4)). _
Value = Sheet1.Range(Cells(n, 1), Cells(n, 1)). _
End(x1Down).Value
Next M
Next n
End Sub
I don't want it to copy and paste the values since I just need the value, not all the extra formatting and what not that is incorporated in the pivot table. When I try to run this I get an error message, saying that there is an application-defined or user-defined error.
View 2 Replies
View Related
Dec 31, 2013
On A3: D10 I have information on C1 a have date and E3:E10 I will input date
i.e. If the date entered in E3 is the same as or larger than the date on C1 then Blank the entire line A3: D3
View 3 Replies
View Related
Mar 1, 2009
I need to calculate the percentage of cells in one column that have data in them compared to those that do not. This is easy enough to do by itself but here's where it gets tricky for me. I need this percentage calculated only when cells in a different column contain data.
Here's the specifics. All of the row and column references are exactly what I'm looking to have in my worksheet. If A5:A200 contain any value then I want to know the % of the cells in F5:F200 that have a value in them compared to the same number in F5:F200 that contain no value. I would like the answer to show up in B2.
Part of the issue is that cells A5:A200 already have a formula in them so I think that would count as a value. The one thing that might help is the fact that A5:A200 values that I'm looking for ALWAYS start with TN so that could be used as the search function. The reason why I need to base it on a value in A5:A200 is that the sheet is blank until I enter data in the rows. I can't have the blank rows below the last data that I've entered on the sheet count towards the percentage that I'm looking for.
Basically it's a sheet that I enter installations and service calls on. The information always has a TN# associated with it. Which through a formula is automatically populated in the same row in the A column. I track the installation or service call and when it's completed I enter a completion date in the F column in the same row.
I am trying to be able to keep track of the percentage of jobs completed (completion date entered in column F) compared to incomplete (cells in column F that have no completion date) but it has to only look at rows that actually have information entered in them which I think should be done by column A. column D5:D200 is where I enter the main information and there is no formula entered in the cells in column D so if it's easier to base it on that column then that's fine.
View 3 Replies
View Related
Jul 6, 2012
i have a price list that I need to upgrade I will show how it is set now and what I need to do.
A1 B1 C1 D1 E1
=E1*1.25 59.99
Result $74.99
when i try to make E1 to a furmula (=E1+10%) it dose not work!!!
I need to add 10% to E1 so A1 is incresed 10% how do I do That
by the way I have much more cells than A1 it gose down to A123 and that is only page one.
View 3 Replies
View Related
Jun 26, 2007
in A1 I have 57 in B1 I have 75 so if I calculate percentage between those two I should get somewhere around 5% instead I'm getting 76%,
what am I doing wrong, in C1 I have =A1/B1 and then formated under percentage.
View 6 Replies
View Related
Dec 5, 2013
Formula "(Cell-Cell)/ABS(Cell)"
When calculating percentage difference do I always subtract the smaller number/cell from the larger number/cell, then divide by the smaller number/cell or vice versa? Maybe I am over thinking it, but it is confusing the heck out of me
I provided an example below to illustrate a spreadsheet I work on. The numbers go up and down. I want to know how I should go about setting up the formula. If I need to ensure the smaller number is always subtracted from the bigger number, it will require a lot of manual intervention on my part.
Row 3 is =(b2-b1)/ABS(b1)Row 4 is =(b2-b1)/ABS(b2)Row 5 is custom. I went through each cell and made sure the smaller number was being subtracted from the larger number, then divided by the smaller number
PercDiffExample.xlsx
View 4 Replies
View Related
Jan 4, 2013
1st Quarter Goal23306.67%
2nd Quarter Goal00.00%
3rd Quarter Goal00.00%
4th Quarter Goal00.00%
The 306% cell has (B2+B3+B4)/7.5 in it to get that value. I need to dump any access percentage above 100% into the next quarters goal. so 2nd should be 100 and 3rd should be 100 and 4th should be 6.67.
View 8 Replies
View Related
Sep 13, 2007
I am trying to apply conditional formatting to multiple columns based on a percentage varience of the value in the first cell of each column. I'm sure this is not a new concept however I have been unable to find any advice when searching. I have attached an example of my problem as a picture speaks a thousand lines of code.
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
Feb 27, 2014
Is it possible through a macro that the cells where numbers is in percentage format should not show any decimal points (is should not roundoff) just decimal points should not visible. I am aware that this can be done by "decrease decimal" but data will be across thousand of rows manually it will be very time consuming.
I have attached a sample worksheet : Percent.xlsx
View 3 Replies
View Related
Dec 15, 2008
I need to calculate the percentage based on self populating score. however I want excel/formula to ignore any cells which has "NA" in it so that it calculates the percentage based only on the cells having Numbers.
Now these Cells which contain Numbers might or might not contain numbers based on the "IF" function which decides wether the cell will have numbers or will remain "blank" or have "NA". To understand better please have a look at the enclosed sample (that would explain my need better)
View 4 Replies
View Related
Apr 19, 2010
I have a column with 10,000+ lines, and lots of them are duplicated.
I have a formula that tells me how many times they are duplicated, but it only looks for 100% matching cells.
Now, lots of cells are partially duplicated, for example:
A1 - Team Abcde
A2 - Team Abcde Fghi
B1 - Team Abc
I would like to have a formula giving me a percentage of how two cells are similar to each others by checking the words. In this case, B1 is 50% similar to A1 because only the word 'Team' is duplicated. B1 is only 33,34% similar to A2.
View 14 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
Oct 6, 2006
I need to divide Cell "A" with Cell "B" and put have the final sum in Cell C. e.g. Cell A===10 Cell B=2 Therefore, I want Cell C to say 5
View 6 Replies
View Related
May 17, 2008
I would like to do the following: * Format numeric cells (cells with a percent sign) to zero decimal places. I have a set of macros that I am combining to clean up a data set and these are two of many (removing blank lines, duplicates, blue/bold, etc.)
View 2 Replies
View Related
Jan 2, 2014
What formula I should use for this condition:
A (Contains many months, e.g. 3 November, 2 December, 10 January, etc. -- in excel date standard format mm/dd/yy)
B (Contains Paid, On process & Waiting for Invoice -- using IF formula)
I want to calculate the percentages of November that has been paid from all of A that contains November in it in column C.
What formula i should use?
I already use =SUMPRODUCT for counting the November.
I only want to use 1 other column to calculate the percentage (C column)
View 4 Replies
View Related
Jun 9, 2014
I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
View 9 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 4, 2009
I am trying to create a formula that shows a percentage of cells within a range that contain a particular number. This percentage is based on another cell, which is the total number of all audits. Each audit has rating columns, with a numeric value indicating it's rating. I need to count those cells within the range that equal the numeric value assigned to the rating, and then divide that total by the total number of audits to obtain a percentage of calls with each particular rating in each category.
Here is an example of what I'm trying to accomplish:
A1 = 2 (Total audits)
B1 = 07/31/2009 (Date of first audit)
C1 = 3 (Rating of first category)
D1 = 1 (Rating of second category)
E1 = 2 (Rating of third category)
F1 = 08/03/2009 (Date of second audit)
G1 = 0 (Rating of first category)
H1 = 2 (Rating of second category)
I1 = 2 (Rating of third category)
The percentage of audits within the first category that have a rating of 3 would be 50%.
The percentage of audits within the second category that have a rating of 3 would be 0%.
The percentage of audits within the third category that have a rating of 2 would be 100%.
View 9 Replies
View Related
Feb 27, 2014
I have the following data
A1 - 65.23
A2 - 1923.52
A3 - 945.18
A4 - 182.54
In column B
B1 =exp(A1)
B4 =exp(A4)
both are fine, however
B2 =exp(A2) - it is showing #NUM
B3 =exp(A3) - it is showing #NUM
Same case for all larger numbers
View 1 Replies
View Related
Feb 5, 2009
I am looking to return the larger value of 2 cells into a seperate cell.
View 2 Replies
View Related
Nov 26, 2012
I have 83,33 value. If I like to increase into 84 and not 83 without decimal point
View 11 Replies
View Related
Dec 12, 2006
I want to do is import a TXT file that has about 70000 rows of numbers into excel. I know it has a max of 65536 so I would like it to import the numbers and put them into 2 columns C and the rest
in D.
Sub ImportLargeFile()
'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, strFullPath As String
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object
'Get a text file name
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...") ........................
View 9 Replies
View Related
Jan 23, 2008
I'm trying to extract a first name and surname from a username :
CN=Joe Bloggs/OU=stneots/OU=EU/O=SAC needs to read Joe Bloggs
CN=Fred Smith/OU=stneots/OU=EU/O=SAC needs to read Fred Smith
CN=Ray Jones/OU=stneots/OU=EU/O=SAC needs to read Ray Jones
The string is always in this format and the name always has a space between. Given a day or so I could probably figure it out, but unfortunately deadlines are breathing down my neck,
View 9 Replies
View Related
Aug 15, 2006
I know function Vlookup finds the largest value less than or equal to the given value. However, If I want to finds the smallest value larger than or equal to the given value, How can I do it? assuming I do not want to use VBA.
View 5 Replies
View Related
Dec 20, 2013
I want to display the percentage of occupancy for each month for condo rentals.
I have columns with dates that represent bookings throughout the year. check in and check out dates to be more precise. (A1 and B1 for example) and I have columns with months Jan, Feb, etc. (C1, D1, etc. for example)
The problem I run in to is that I can calculate the number of days between the dates and the fraction of the year (*12 to get a monthly value, but I fail to understand how I can break that up so that I can populate each month with a correct percentage of the total term.
E.g. A1=1-jan-2014 and B1=20-jan-2014 is an easy one because it only covers 1 month:
formula: =(YEARFRAC(A1,B1,3))*12 gives me 62,47% which I could just use as is for that month.
So far so good, but when the period covers more than one month or when the months overlaps, I don't know how to break the outcome up into the appropriate months to display the correct percentage for each month in its own column.
e.g. A1=1-jan-2014 and B1=20-Mar-2014 gives me a result of 253% with the same formula.
100% for November, 100% for December and 63% for January, which I could somehow formulate to break up over different Months.
But then it gets more tricky. What if the booking starts on a day other than the 1st of the Month?
e.g. A1=5-jan-2014 and B1=20-Mar-2014
The result of the formula (243%) is correct, but isn't sufficient to put the correct percentages for each month in their respective column.
What formula(s) should I use to break down the percentages to match the correct fraction of each individual month?
I attached an example of what I have so far for your review : test.xlsx‎
View 8 Replies
View Related
