I recieve cost sheets from a company and I want to be able to change there number into a formula and mark it up all in one. How would I do this without going into each cell individually and make the change? Example say the number is $100 in the cell I want to change that to =$100/.6 to get a 40% margin. Then I can give this to salesmen with just the retail number but I can see the formula and cost.
View 9 RepliesI run the following formula down a column of records in row AR
=IF(COUNTIF(lookup!A:A,N2)>0, "Exclude", "Not In Lookup")
I want to add to this formula an additional function that if any cells in column A1 are blank, then do not perform the remainder of the equation.
This is what I am using:
=VLOOKUP($A5,TEAM!$B:$MZ,23,FALSE)
When I select the cell formula extension button thing and drag it to the right, the formula copies exactly into the next cell. If I select multiple cells starting from 19-23 and then drag, excel just repeats the formula over, (19, 20, 21, 22, 23, 19, 20, 21, 22, 23, 19, 20...) This is seriously frustrating as I have to click into the formula bar on each cell and change the value of the column lookup.
I can't keep going manually as I need to go to about 300 cells...
I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
I have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.
=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.
My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)
This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")
I want to add across columns, which is not so difficult but I have to "ignore" the blank cells because of the way my formula works.
I'm not sure of the correct mathematical term for the addition I have to do but here is my example:
in this row are the values
1 2 3 4 5
In the row below I want to add the numbers so the result is
1 3 6 10 15
basically, a cell is always adding itself to the result of the sum from the previous cells. This formula I can handle, (=A2+B1 then autofill the results by dragging the formula across the empty cells) but when there are blank cells between values I get the error message! value. How can I write a formula which will ignore the empty cells?
i have a problem adding cells every 5th cell for example i have
=SUMPRODUCT((C10:C67)*(MOD(ROW(C10:C67),5)=0))
this adds every 5th row in this range and it does it right as long as there is no text in the cells in between
it works fine when it looks like this
1
1
1
1
1
1
1
but it does not work when it looks like this
1
1
text
1
1
1
when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them
i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes.....
My problem is that I want to select a cell with a "Sum" formula ex.
View 5 Replies View Relatedhere is my formula
=SUMPRODUCT((MONTH($A$10:$A$624)=1)*($C$10:$C$624="DO"))
I want to add this:
($C$10:$C$624="DO")) AND: "SD" AND "V"
how would i add these other two factors?
I need a formula to know the total specially for each of the numbers in column A. Like for e.g when you see 1 in column A I need to to know the total by adding all the numbers just for 1 in column B. So the total for 1 in this sheet would be 10+85+5=100. So automatically the answer should come as 100.
View 11 Replies View RelatedAverage in Cell J8 : J73
Handicap in Cell I8 : I73
What Im trying to achieve is the Lowest Average Plus handicap 1 = total
Example
Cell
J8 = 105
J9 = 169
J10 = 126
J11 = 155
Cell
I8 = 45
I9 = 35
I10 = 54
I11 = 21
Result 105 + 45 =150 1 = 149
My formula is : =SUM(C8:N8). How do I add a "note" as opposed to a comment to this formula?
View 3 Replies View RelatedI have a number of cells, with a rule as follows, but within each cell the company name and month vary, over 836 cells, so I cannot do a find and replace so wanted to know if there was a way of adding the following part to the rest of the formula within the 836 cells in one go, rather than tediously going through each cell and copying/pasting.
The part I would like to add to the various cells - 'Master Quote Sheet'!$H$18:$H$6021, "Won"
Existing formula (the varying parts within the different cells are the "02.2014" and "Company Name"
=SUMIFS('Master Quote Sheet'!$J$18:$J$6021, 'Master Quote Sheet'!$E$18:$E$6021, "Direct", 'Master Quote Sheet'!$A$18:$A$6021, ".02.2014", 'Master Quote Sheet'!$D$18:$D$6021, "Company Name")
I am desperately trying to add a simple subtotal formula but receive the same error ("Type mismatch"):
View 4 Replies View RelatedI need a code that looks through Column A (account numbers) then adds up the profit from Column X for each account number. The total profit for each account is then sent to different sheets. Account 100 profit sent to Sheet S1, Account 200 profit to Sheet S2, and so on
.The profit figure will go to Column C (Sheets S1,S2,S3..), in the next empty cell.
I have a code here that works, but problem is it places a formula in the cells in the output sheets. I dont want any formula there, just the value that is calculated. This is because everyday there will be new values in the 'Summary' sheet, and if the macro inserts the same formula everyday I data(profit figure) from the previous day gets distorted.
I am trying to insert a formula into a VBA Code but I get an error msg.
The formula is;
Currently have a formula to enter the last value in a row in cell B1. What i need is the formula below in B1 then a formula in A1 which will show the value before the last entry in row 1. Maybe an offset unless there is an easier way
=INDEX(1:1,MAX(IF(D1:Z10,COLUMN(D1:Z))))
i.e.If H1 was the last entry in the row I want a formula for H1 in cell B1 and a formula for G1 in A1
I have the following code which works plus a formula in cell range G5:G123....how do i add the the formula to the VBA code
Formula =IF(F5"".F5< today () +3 )
I'm making a basic spreadsheet that has to calculate monthly due dates for 'reviews' based on an initial start date.
im using:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
.... i know thats probably dead simple to you guys!
which works fine.. except when it comes to times when the start date is for example 31 january, so the sequence goes:
31 jan....... 03 March.... 31 march
and my problem is i need 1 review in each calendar month, so i need to tell that 03 March to be 28 Feb instead.
Here is my current formula:
=IF(ISERROR(INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0))),"0",INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0)))
I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.
Is it possible to have in cell A1 "Sales Reviewed Weekending" and then the formula =TODAY()-WEEKDAY(TODAY())+1) all together?
View 9 Replies View RelatedField 1 shows as MON 02/06/08 on sheet 1
I would like it so that Field 1 shows TUES 03/06/08 on sheet 2 and so on for every sheet for the remainder of the month.
I would also like it if I could just edit the first sheet then hit print 31 copies and have it count up each for each sheet that is printed.
Or another alternative is to change sheet 1 and have every sheet after that change in succession and then just print the entire workbook.
I have data that is exported to an excel spreadsheet from the phone switch for Average ACD time and Average ACW. Example: one cell is value :07:01 and and the next is :00:42.
I want to add the two together to get :07:43 or 07:43 for AHT. I have tried formatting the cell with custom time and time formats, but I get #Value. I believe the ":" at the beginning is not recognized. What formula can I used to get the two cells to sum?
I have created a survey on excel. When completing the survey, the customer will have to place an "X" in the appropriate column (very satisfied, satisfied, dissatisfied, etc.) Is there a formula in excel that will allow me to add the number of responses ("X's") for Very Satisfied, Satisfied, etc.???
I have this formula in my spreadsheet =IF(ISBLANK(B2), "", C1+1). I need to add a "dash T" to this formula so it shows up in every cell. How and where do i write that into a formula?
View 2 Replies View RelatedI m adding cell with alphabets. I attach a pic of what i want it to look like. Is there a command to do it for you instead of me outting one by one myself? Ex. 'A' must first followed by 'B' and so on, futhermore, if there a CApital 'A' and a lower case 'a' the capital 'A' goes first.
View 14 Replies View RelatedI have a quotation that I am working on that I need to add 12.5% onto each seperate line price.
There are 300 individual lines on the quote all with different pricing but they all need 12.5% adding on so I am hoping there is a simple way to do a bulk sum for all of the rows rather than having to go through each row and creating a sum for each. - ie =(K28*1.125)
I'm trying to manage my Stock by using protected sheet, but i stuck with this "row adding with formula" stuff.
The user can only write in the colored cell, but everytime the user add new row, the formula doesn't copying by itself
I attach the file below with no password, if it's protected, the password is none, just press enter ( password : "" )
Book2.xls
I am using the formula =IF(OR(E2="COLD"),"33%")
Which changes my cell to show the text 33% if the text cold is entered into cell E2. Now what I would like to know, is if I can add multiple catch words to give alternate pre defined percentages. Such as warm and hot to give the respective answers as 66% and 99%