What I need is a formula for the following:
If Column A equals "No Show" and Column B equals "Anthem" then sum of cell C2 divided by D2
Every time I input this simple little formula I get an error.
A
B
C
D
Status
Coverage
Counts
No Shows
No Show
Anthem
5
10
I am fairly new to spreadsheet and need to develop a simple spreadsheet that will present a actual vs budget % for certain clients on a weekly basis.
It is currently taking me forever to remap my formula each week when i have populated my actual vs budget data.
On Sheet 1 it has my data and on Sheet 2 is where my ratios are presented with graphs.
On Sheet 2, the ratio is presented in one cell with a formula that calculates based on two cells adjacent to each other on Sheet 1.
I am currently running my report horizontally across the spreadsheet.
The problem is that when i add in another week running horizontally across my spread, the formula can not be dragged across as it does not jump by two cells.
I have attached a example : example.xlsx‎
The attached worksheet is for giving competitors a score of 1 if they meet certain criteria and 0 if they don’t. Need to write a formula that warns me if the three highlighted ratios are not meet. The last Ratio (15) is on a sliding scale so must be at least 1.
View 6 Replies View RelatedI am trying to creat a summary sheet to an attendance log that goes back several years. Idealy, I want to be able to set a date range in the formula that will show me a count of how many absenses, lates, or leave earlies. The table I am taking the data from has columns from left to right (Date, Absense, # of Absenses, Late, Duration of, Left Early, Duration of, and MP) For example in column A I want to see the date Range of 10/1/2013 to 4/1/2014 and I want a count/sum of each of the categories.
View 5 Replies View RelatedI have a macro that will create 15 reports based on a filter in a Pivot Table and will save them to a folder based on the filter names. This work great, however in one of the reports I have a few vlookups as an example of one of them,
Code:
=VLOOKUP(Selector,Trends2!$B$6:$LV$22,AI50,FALSE)
. Now when the report is created from the macro, the vlookup is changed to,
Code:
=VLOOKUP(Selector,'Z:ISB DataISR RASC non transactional dataISR dataNational and Zone Reports[ISR National and Zone Template v18a.xlsm]Trends2'!$B$6:$LV$22,AI50,FALSE)
Is there any way to stop this from happening? Is it a general thing in Excel? Code below that creates the reports if needed
Code:
Sub Create_National_Report()
'Start of report creation process
Sheets("Pivot Tables (2)").Select
[Code]....
I have seen a few posts that are close to this but not quite right. I have a situation where I need to do "=LEN..." formula that counts trailing zeros at the end of numbers (meaning zeros that are displayed at the end of a number but do not appear in the actual cell value).
example:
Cell E19 contains the actual value 4773.52, but it is displayed in currency format with FOUR digits so it displays as $4,773.5200.
The formula I am using is =LEN(MID(E19,FIND(".",E19)+1,4)). The result is 2, which means it does not include the last two trailing zeros in the LEN count.
Is there anyway to get it to include the trailing zeros so the result equals 4? Either w/formula, macro, or UDF?
Is weird I know, but I need to know how many decimal places (displayed, not actual) for many numbers on many sheets that I do not create myself.
I have a document that I created that has merged cells. In order to autofit the rows of this document, I referenced the merged cells in singular cells that are not visible to the users of the document. (They are not hidden)
It had been working relatively nicely, but now the autofit function is not working correctly. If I copy and paste values into the cell it will work, but this would not be my ideal solution. I have tried playing with the number format, which works for the most part if I set it to "&" - text. It doesn't work for some longer cells with line breaks in them.
Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days.
I am using Excel 2010.
I am calculating ratios to show as 1:0 or 3:2 etc using this formula: =IFERROR(CONCATENATE((K2/GCD(K2,N2)),":",((N2/GCD(K2,N2)))),"Nil")... I have a lot of columns that have zeros in them and the formula i am using doesn't seem to work for those...
Example
A B
3 6 should equal: 1:2 works OK
2 0 should equal: 2:0 my current result: 1:0
3 0 should equal: 3:0 my current result: 1:0
0 1 should equal: 0:1 works OK
0 0 should equal: Nil works OK
4 1 should equal: 4:1 works OK
Anything with a zero in the first column comes up as 1:0
I am trying to work out how to convert the slope/gradient from degrees to %, and ratio.
View 7 Replies View RelatedThis is what I’ve been trying to do. There are 3 different work schedules for each employee. Each employee gets 2 days off in a row. At the top of the employee schedule worksheet, there are 3 schedules. The zeroes represents days off for that schedule and the 1’s represents days worked. Ok, Cells c7 thru c17 display total staff needed for that day Cells c8 thru c18 display average number of customer on a particular day Cells c9- 19 display number of needed based on a staff to customer ratio of 1 employee per 80 customers B11 displays that ratio
I need to make a comparison of two different staff to customer ratios. My goal is to find lowest total payroll while adequately staffing the store. In B13 I want to enter the daily salary amount for 1 employee at $10 hr/8 hrs per day
In A16 enter total payroll amount
Total staff B7 times salary in B13
Use solver to find minimum payroll amount
Target cell is total payroll found in A16
Target cell must be = to minimum value................
Can we unmerge a complete address in seperate seperate coloumns? Like Complete address is "1st Floor, 2a Harrison Road, Erdington, West Midlands, B24 9AA" and i want to split it like Address[1] "1st Floor" , Address[2]: "2a Harrison Road", Address[3]: "Erdington"...
Is there is any formula which can split data with the help of seperators (Comma or space) ??
EXCEL FORMULA CHALLENGE:Needs a formula like as above , expand the formula to generate from One to Nine Hundred Ninety Nine Thousand Nine Hundred & Ninety Nine? 1 to 999,999,999 all numbers into words between this range ?
View 3 Replies View RelatedI have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies View RelatedI need a formula to count the total number words occurred in an array. It has to separate the "," and count.
Here is a scenario:
1.Apple
2.Banana
3.Orange
4.Mango
5.Banana
6.Mango
7.Orange
8.Apple, Banana
9.Banana, Mango
10.Mango, Apple
11.Banana
Excel. Let's say this how a column looks like:
a
b
b
c
d
Is there a formula to get this outcome:
a 1
b 2
c 1
d 1
If not, is there a way to create a list of words that are in a column?
I'm trying to find a formula to add spaces in between capitalized words
example :
cell A1 = MtVernonRoad
trying to make it say..
Mt Vernon Road
If I have ABC DEF in a1, is there a formula I can put in a2 to remove the space and display ABCDEF
View 2 Replies View RelatedThe formula cant always match words from the arrayand gives me N/A
I have attached the formula i am trying to use . Is there an earier way anyone can recommend. Im sure im over complicating things
There are five levels of Performance.
Not Evident(o), Emerging(1),Operational(2),Highly Functional(4),Exemplary(5)
There are ten rows of Categories of Performance.
There is one final column of Performance Summary for each category.
There are seven columns that are report sources of Performance.
There is one final row of Performance Summary for each report source.
There is one cell that is a final Summary of Performance Overall.
The cells have drop down lists of words only.
I need the Summary cells to show the result of calculation of the average for each row and column.
Is there a formula that can count the number of words in a cell?
View 3 Replies View RelatedI have a column filled dates in the format 01 January 2013 and I want a formula to count all the cells containing "January" for example.
View 3 Replies View RelatedI need a a formula that will count the number of times a word or phrase appears in a column of cells and assign a number
For example....
Big Red Ford Truck = 1
Ford Truck = 3
Red Ford Truck = 2
After i get the formula, I can sort by "greater than 2"
I'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
Formula that will convert uppercase words longer than three letters, into lowercase. Leaving the rest of the string unchanged?
For example,
Mary and John at IBM thought this was a GREAT OPPORTUNITY. Becomes:
Mary and John at IBM thought this was a great opportunity.
I have also tried using more func's REGEX.SUBSTITUTE but I couldn't get that to work either!
"Use a formula to fill in column F (brand name) in the data worksheet. The Brand Name is the Branded Description minus the last word.
NOTE extra mark: If your formula can’t find a space (is error = true) then it takes whatever is in the cell and uses that."
Would I be using the CONCANATE formula or something similar?
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311I="*L/S*")*(Str311G<>" DM "),Str311L)
The formula contained inside the names are:
Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)
Str311A=OFFSET(Str311,0,0)
Str311C=OFFSET(Str311,0,2)
Str311I=OFFSET(Str311,0,8)
Str311G=OFFSET(Str311,0,6)
What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.
Code
Description
Rate
Formulas
123
Afghanistan
0.07
=MAX((B2:B30000="*Afghanistan*")*(C2:C30000))
[code].....
I am trying to find the Maximum Value in Column C based on the criteria that Column B contains "Afghanistan but NOT Mobile.
In a second cell I also want to find the maximum value in Column C based on the criteria that Column B contains "Afghanistan" AND "Mobile"
The code I have come up with is in the table however it is only showing the max value for cells containing "Afghanistan". If I add the wildcard "*" it does not return a value. how I can achieve my objectives?