Formula If 2 Cells Used
Mar 15, 2009im looking for formula to insert the word "or"
if
d22 and d22 have data in. if only one of them has data in then to leave it blank
ADVERTISEMENT
Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula
Dec 22, 2009Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
How To Copy Row With Formula In Locked Cells And Insert Copied Cells In Protected Sheet
Mar 29, 2014Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies View RelatedWhen Dragging Formula Down The Formula Skips X Number Of Cells
Jul 22, 2014Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:
A1= "1"
A2= "2"
A3= "3"
Then I type in the simple formula in the cell
B1= "=A1"
and then drag down but it skips 3 cells so then
B5= "=A2"
B9= "=A3"
If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"
this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.
Something similar to this B1 "=A1(skip3cells)"
Formula Arrays That Will Omit Empty Cells In My Formula
Jan 31, 2008I am looking to average a range of cells which won't always be the same size. How do I create a formula array that will omit empty cells in my formula.
View 9 Replies View RelatedFormula That Links Cells In Tables Together With Cells Not In Table And Give Sum
Apr 18, 2014What I'm trying to do is link a cell to give me the sum of 2 different cells located in 2 different tables I created.
So I want to have Cell B5 look for the number in cell B3 in the Table that starts in column Q1 and give me the corresponding number in the same row in the column R1
then lookup the number in cell B1 in the Table that starts in column T1 and match it with the number in Column U1 that corresponds to it in the same row
and Finally give me the sum of both corresponding numbers.
IE.. the number in B3 is found in Q13 which uses the number it corresponds to in R13. The takes B1 finds it in T16 and uses the number it corresponds to in U16.
Then returns the sum of R13 and X16 into B5. (B5 should show 176)
Cells Not Being Populated With Exact Same Formula As Previous Working Cells
Jun 27, 2008formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula
View 9 Replies View RelatedHow To Protect Formula Cells But Allow Access To Data Cells
Dec 22, 2011I try this method but data entry not allowed
Here is a scenario to protect
formula cells but allow access to data cells:
. Select all cells in the worksheet by clicking on the top left corner of the sheet.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Uncheck the Locked and
Hidden check boxes, then click OK.
These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to
protect the formula cells, and here is how we do it:
. Select all cells that have formulas.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Check the Locked and Hidden check boxes, then click OK.
. On the Tools menu click
Protection and select Protect sheet
Highlight/Flag Formula Cells Referencing Other Cells
Sep 27, 2006I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
View 3 Replies View RelatedIF Formula Assistance: Formula That Looks At 2 Adjacent Cells
Feb 2, 2009I need to create a formula that looks at 2 adjacent cells. If both cells show 'YES' then I need the 3 cell to show 'YES'. If either/both of the 2 adjacent cells are blank then the third needs to be blank.
View 2 Replies View RelatedCells Display Formula, Not The Results Of The Formula
May 29, 2009To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?
Example of formula:
=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))
Variable # Of Cells That Need Formula Matching # Of Cells
May 6, 2009I am new to Excel macros and programming, and am running into a problem that I can't figure out how to solve. I have read through the forum and tried various examples, but I am still not getting the result I am looking for.
I have a spreadsheet that has a variable number of cells in-between blank rows (the data can be 1 to 6 rows). I need to sum up the rows of two columns and see if they equal 0 or if there is a difference. If there is a difference, I want to make the cell red. The exact problem I am having is I can't make it check the # of rows and run the formula based on that number of rows. I am attaching an example of my raw data followed by an example of what I am looking for as the end result.
Formula Referencing Cells That Contain Another Formula?
Mar 3, 2014I've come across a problem I can't seem to solve. I have a cell that contains =AVERAGE(D8:AH8) which was working fine. I made some changes to my sheet and now D8 through AH8 contain a number arrived at through a formula ( for example =D6/D7 ) instead of just a hard number, and my average formula no longer works.
View 3 Replies View RelatedFormula Changes To Value - Need To Copy As Formula To Other Cells
May 22, 2014I have searched and unable to find how to keep a formula as a formula in a cell so I can copy it to other cells. Every time I enter a formula, example: =if(a2>0,trim(v2)&"."&trim(x2),"") the cell immediately displays the value and I am unable to copy the formula to other cells because I get the same value all the way down the sheet. Example star.plus, star.plus, star.plus... Every cell in the range should have a different value and change whenever the cells in column v or x change. I also tried entering this formula on another workbook which displays correctly the formula in a cell and then copying and attempting to paste onto the existing worksheet but the past and paste special are greyed out.
View 3 Replies View RelatedCopy Pairs From 2 Columns To Formula Reference Cells & Copy Updated Formula Results
Jun 24, 2008I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies View RelatedFormula To Sum Cells Only When All Cells Contain Values
Jul 31, 2013I have a column of cells that the user enters values in ... say cells A1 through A10. Once all cells have values in them, I want cell A11 to display the sum. The formula I'm trying to use is:
{=IF(ISBLANK(A1:A10),"",Sum(A1:A10))}
But this is putting the sum in A11 as soon as any cell has a value in it.
Is there an easy way with a formula to require all ten cells to have values?
Formula To Average Range Of Cells And Copying It To Calculate Next Range Of Cells?
Mar 17, 2014I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
Formula To ID Cells Containing A Formula
Oct 5, 2008I need to identify cells that contain a formula so I can use conditional formatting to colour those without formula.
Most cells in my array contain a formula but occasionally I have to over write the result or enter a result manually because a vlookup is returning an error.
How To Sum Up The Cells Which With (sumif) Formula
Apr 21, 2014i am using "sumif" formula in cell C5 and C10, the next step is to add up these two cells (sum up the figures of "sumif"), then the answer will be +15+6=21, how to do with using single formula.
View 7 Replies View RelatedConditional Formula For Row With 10+ Cells?
Jul 28, 2014I am making the monthly schedule for my colegues. I wonder if there is a conditional formula that can highlight a row with 10+ cells if in one of them I type SUNDAY or something.
I am attaching an example of the schedule. I thing you will get my point.
Lock Cells After Done With Formula?
Feb 8, 2014I use the attached workbook to keep tack of feed use in turkeys. when a new flock is added to a farm (Airport View or Home farm) A template is copied and pasted from the "templates" sheet depending on what type of diet they will be on. (Ralco ABF or Ralco Standard) that template refers to the Ration sheet to calculate how much corn is in each diet. After a flock is complete I will never change the numbers again, it is just for record keeping. I want to be able to change the Corn/ton numbers in a ration (on the rations tab) if need be and not affect all the older completed records.
View 1 Replies View RelatedFormula To Add Cells Between Tabs
Feb 12, 2014I need a formula to add cells between tabs
View 5 Replies View RelatedReferencing Cells Not Next To Each Other From A Formula?
Mar 26, 2014I have a long list of amounts one per day. Sun-Sat repeating. I Total the amounts for each week and place it next to Sun. now i have the total for that billing week.
I need to reference these numbers from a different sheet that only has one row per week. I tried to do ='sheet1'!a2 then next row down ='sheet1'!a9 and ='sheet1'!a16 etc then fill down but when i do it seems to start again and do a3, a10 and a17.
Is there a way I can get it to only use cells with figures in them? IE if cell is empty then move down one else use the amount in cell... or how do I get it to reference 7 cells below each time rather than the next immediate cell.
Formula- Blank Cells
Jan 22, 2008i've got a pricing spreadsheet that's doing my head in. hopefully someone can help me with my latest formula issue.
when i expor the file as a pdf, you loose the column names. so...
a b c d e f g h
part ID qty 1 price qty 2 price qty 3 price discount price
i want to calculate the discount price as a perctange of either b/c or d/e or f/g groupinsg (qty break & price for that paricular qty amt). The problem is b/c are populated for all rows. some parts have a secdonary price break (d/e) and some have a tiertary price break (f/g).
I want to take the percentge discount off the high qty price break. So if f/g is populated I want to take it from that. If it's not populated, then e/f. and Only if f/g & e/f are NOT populated do i want to take the discount from b/c. is there a formula i can insert into H to do this?
Protected All Cells With Formula
Oct 20, 2008I've created a spreadsheet that I want to offer for sale.
I've protected all cells with formula and those not needed to use the program. The only cells that can be selected/changed are those that need the values for the calculations.
How do I protect the sheet from being saved/stolen when trialed by possible customers?
Formula To Fill In Cells
Aug 12, 2009I am trying to create a nice equation that will look in row 35 for the highest value and then return the value that is 33 rows up and 1 column to the left. See attachment of for more details.
Basically, I need a formula to fill in cells B38:B41 (detail included in Excel doc attached).
Summing Cells Where One Has A Formula
Dec 25, 2009I am attempting to use the if function that looks at a cell to see if it is a certain value and if so it adds the value of the cell above with another cell.
Here is the formula in cell AH26: =IF(G26=Variables!$F$4,AH25+AF26,AH25)
the problem is if the G25 did not equal Variables!F4 then AH25 is basically zero or the formula so when cell G26 does equal Variables!F4 then AH26 is supposed to sum AH25 with AF26 but AH25 cell's contents are a formula. How do I get it to ignore the formula and add AF26 with zero instead of the formula?
Formula Errors In Some Cells
Feb 17, 2014The following formulas is showing error in some cells?
=IF(ISTEXT(PRONOSTICOS!G5), PRONOSTICOS!G5, "")
Variable = A Sum- Formula To Add The Cells
Mar 31, 2007ActiveCell = "=(SUM(R3C5:R[0]C[-3]))"
In a macro I use that formula to add the cells of column E (or 5) from Row 3 down to the row of the active cell which is in column H (or 8), (the active cell would be H9 in this instance); and the result goes to the active cell as the formula =SUM ($E$3:E9).
But I want the result to go directly to a numeric variable, not to a cell. Something equivalent to this:
a=(SUM(R3C5:R[0]C[-3])), which is not correct.
What would the correct syntax be?
If Statement (put 'IF Formula' In Cells)
Sep 23, 2008I am trying to put an "If statement formula" within cells using VBA but it errors at the word "error".
Basically what I am trying to do is put similar to the following example into a range of cells.. for example:
=IF(LEN(Verbatim_LU!B2)>100,"ERROR","OK")
...with B2 being worked out by the for..with and the value (in this instance 100) being put in by VerbLen.
NB: Column A contains a reference number hence not having this actioned on it and counts start from 2