Calculating Percentage In Cell And Omitting Values When Specific Words Present
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.
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.
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.
I have two lists, one that is 99,000 lines and the other is 150. I am trying to find out where the word(s) in the list of 150 is present in the list 99,000.
1st list of 99,000 is in Sheet1 A1-A99000 and the second list is in Sheet2 B1-B150.
The caveat is that in column A there is additional words in that cell so you can not do a simple vlookup, because there may or may not be an exact match.
I have been stuck for hours and the best i can get is to use the match function but it is not working because it is not exact. Last thing i want to do is use the CTRL F key to lookup all the words.
I have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
How to use the data tables function under the what-if analysis.
My sheet and calculations are all set up
But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV.
Ive done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
Basically, I want a formula that searches for more than one word anywhere within a cell, and if the cell contains any of those words somewhere in it then I want to display a value, but if the words aren't there, then stay blank.
For example, my cell could say: "Magenta, Rose, Lime, Lavendar". I want to search this cell for any of the these words: "Lime", "Forest" or "Teal". If any of these appear, I then want the cell with this formula to say "Green".
I know the formula to search for only one word /phrase in a cell, I use: =(IF(ISNUMBER(SEARCH("yellow",A1)),"colour","-")), (i.e. if the word "yellow" appears anywhere in cell A1, then this cell will show the word "colour", if not then a "-".) I tried adding an 'OR' function in this to search multiple words but it didn't seem to work.
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
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.
is there a formula that will find a word in a sentence written in a cell and if present then enter specif text in another cell? for example, if the word "Hotel" appears in the sentence in cell A5 then put in the text "Hotel and subsistence" in cell B5...
I'm trying to write a macro to update a report including deleting all rows that do not contain a specific value of TP in column K. Below is one of the examples.
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
I need a formula that will tell me how many minutes/hours are needed to hit a certain percentage.
For example, I need to do 60% of my time doing one thing (Team), and 40% of my time doing another (Individual). So, I need to know, if I've spent 5.5 hours doing Team, and 4 hours doing Individual, I need to know that I need to do .5 hours to hit the correct split.
I can't figure out what formula to use that will increase the current total to best figure out how many minutes I'll need to do either Team or Individual to hit the 60/40 split. It doesn't need to actually tell me 15 minutes, etc, but .25 would be fine (everything is done in .25 increments).
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)
I was quite a few problems using worksheetfunction.ln() to calculate % change. I decided to write more basic code to try to accomplish this but I'm getting an overflow error.
Code: DRows = WorksheetFunction.CountA(Sheets("INFO").Range("A:A")) For r = 1 To (DRows - 1) '***** -1 is bc of ln property percentchange = 0
I have columns like L2, M2, N2, P2, Q2, R2. For each row in this sheet, I want to get the average of L2:P2 and place them into R2. while omitting Q2. How do i put this in my code?
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?
Good afternoon Gentlemen, I have a column of data, with a varying number of percentage values that add up to 100%, separated by "NA", i.e.
33% 33% 33% NA 25% 25% 25% 25% NA 100% NA 20% 20% 20% 20% 20% NA
Now... when I change one of the values I would like the others to even up, i.e. in the last example if I change a 20% to 50% I would like the others to change to 10%... any ideas?
How would I write a formula that takes cell values from six worksheets and adds them up as a percentage (quarterly updates)? I realize this is wrong, but here's what I'd tried (for three sheets).
I need to dynamically build a formula for calculating a running percentage for a row of data. The columns that are referenced by the formulas are static (f thru r), but the row number can change depending the number of rows of data. I have a set of data that can have one to many lines in it (the data is placed on the worksheet using vba code). After the data is placed, a total line is built using this
'Find row number for total row (2 rows down from last row of data) endrow = Range("a35").End(xlDown).Row sumrow = endrow + 2
'Set sum formula for the "e" column Range("e" & sumrow).Formula = "=sum(e35:e" & endrow & ")"
'copy total formulas to columns f thru r...............................
I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.
Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)
Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...
Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:
Function MyMerge(Rng As Range) For Each Cell In Rng Temp = Temp & Cell.Value & ", " Next Cell Temp = Mid(Temp, 1, Len(Temp) - 2) MyMerge = Temp End Function
Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469. Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...
If you want to create a FOR...NEXT loop for values that are present, how would you go about it? Sometimes you may have 1,2,6,8,12,13 and the next 2,3,5,6,7,10,11,14. It may be a number between a certain range, but the sequence and step will change every time (for trying to get a Auto-filter to cull a list based on descriptor)
I have 2 columns with approximately 200K lines of data. There are only 84 and 823 unique values in each column respectively. On my report.
Column A is essentially a customer list, column B is a product list. I am trying to find out how many unique/individual products a customer orders. I have a separate column that has the customers in it (Column G), and in Column H, I want it to count the unique products ordered by customer.
I changed some of the data, but essentially what I attached is what I need.
I have 1000 diff names in a spread sheet and wish to search another spread sheet with these names on and alot more.
I want the search to look in the larger spread sheet for these 1000 names and copy all the informtaion in the cells to a new sheet, if the name is not found i would like something like "no info found" to be seen.
Im a novice to doing this sort of thing in excel, but i have found this script which searches for the word "mail box" and copies all the info in the cell when it finds it.
I have two sources of information that I need to look up on, but one of the sources contains other words within the same cell?
For Example I have a source of data which contains all our client names, and I need to search for the client name in a second source of data to bring through the value from another cell, however the table array that I would do the look up on contains both the client name and project name together in one cell - therefore I am unable to do a normal Vlookup - also the client name is not always the same number of letters.
See an example:
Source data 1: Client name: XYZ Ltd
Source data 2: in cell A1 XYZ Ltd/project ABC in cell A2 shows the value e.g.: £5000
Would there be anyway to do a look up on XYZ Ltd to bring through the value in A2 on source data 1?
Above is the formula I'm trying to get to work but it's not-a-happenin. It seems like the first IF overides all of the others. Basically I want the first IF to be ignored if column G contains any of the words shown. So basically what I think is happening is that P3 is less than Q1 so it's ignoring the rest of the IFs. Originally my formula worked fine as:
=IF(P4<$Q$1,E4-S4,"Not Changed")
But I realized that i needed a certain category of product ignored. Products are labeled in column G.
I am in need of a forumula. I am trying to extract one word before a keyword, one word after a keyword, 2 words before a keyword, and 2 words after a keyword.
For example:
The keyword is blue:
birds are blue today - 1 word before=are; 1 word after=today; 2 words before=birds are; 2 words after=today
blue is my favorite color - 1 word before=(null); 1 word after=is; 2 words before=(null); 2 words after=is my
see blue colors in the sky - 1 word before=see; 1 word after=colors; 2 words before=see; 2 words after=colors in
I am trying to extract numbers after a specific text in a text, for eg :
abc SN 12345 xyz edf SN No. 456 mno
As per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)
