Add 2 Conditions To Formula Or Modify Existing Formula?

Dec 22, 2012

I'm trying to add either a new condition to a formula or modify existing formula, so blank cells can't be taken as a number like 0 Here is the formula =IF(N14>0,1,0)

***This is what I do with formulas with similar problems =IF(COUNT(Y14,A14)=2,Y14/A14*100,"")

unfortunately the above condition its already a condition by itself so I don't know what to do whether to modify condition or add a new condition which I don't know how to do.

View 9 Replies


ADVERTISEMENT

Add Additional Formula To Existing Formula To Exclude Weekends And Holidays?

Mar 27, 2014

I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.

View 2 Replies View Related

Add Addition If Condition To Existing Formula: Long Formula

Aug 12, 2007

This task joins a string together based on a number of characters per cell in the range.

I want to isolate one range, Col N, and add an IF condition to it.

There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....

View 9 Replies View Related

Add Total $ Value Formula Into Existing SUMIFS Formula?

Apr 3, 2014

In my attached spreadsheet example below, in the Rollup Data sheet, I have a formula (Cell: C17) that will give me the actual cost totals that has been invoiced on WBS Element (DWRRI-BW096-231) for a total of $35,004.81.

What I need the formula do is run a redundant check procedure on my new WBS Element (DWRRI-BW096-231) actual cost total formula in the Rollup Data sheet (Cell: C17) against/vs. what the SAP system provides in the raw SAP Excel export contained in the PTD-Actual Cost sheet for the WBS Element (DWRRI-BW096-231) actual cost total of $35,004.81 (Cell: F9) that's highlighted in the yellow row.

In the Rollup Data sheet (Cell: C18), I need this formula to perform a similar function the as the previous formula above it (Cell: C17), but it this formulas function (Cell: C18) would have a separate operation that would only look for and return the single/sole WBS Element (DWRRI-BW096-231) actual cost total contained in the PTD-Actual Cost sheet (Cell: F9).

In the Rollup Data sheet I could then compare my two WBS Element (DWRRI-BW096-231) actual costs totals, the first from the automated addition of all the individual matching WBS Element actual costs into an actual cost total (Cell: C17), the second (new formula I'm requesting) from the matching and return function of the single/sole WBS Element actual cost total (Cell: C18).

So, if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both equal $35,004.81, the formula in C17 is a success and has correctly identified and added all the actual costs contained in the PTD-Actual Cost sheet, but if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both do not equal $35,004.81, the formula in C17 failed for some reason and has not correctly identified and added all the required actual costs contained in the PTD-Actual Cost sheet.

How to Add Total $ Value Formula into an Existing SUMIFS Formula.xlsx

View 2 Replies View Related

Date Formula & Add If Statement To The Existing Formula

Dec 19, 2008

I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?

View 2 Replies View Related

Modify Formula From Sum To Count

Aug 8, 2008

Hi I have these formulas that look in a specific column and if the argument is true then it sums the numbers in the same row but different column.

I need it to count instead of sum. These are array formulas so in order for them to currently work you must press ctrl shift enter.

{=SUM(IF('sheet1'!I7:I510>D7,'sheet1l'!G7:G510,0))}

{=SUM(IF( 'sheet1'!I7:I510>B8,IF( 'sheet1'!I7:I510

View 9 Replies View Related

Modify Series Formula

Jan 2, 2009

I'm constructing a dynamic chart and I'm having difficulty naming the series formula.

I wish the Y values to pick up values in a different sheet and have entered the following formula :
=Rnd4Projects.xls!,Timeline!ChtYLeics

Workbook is called Rnd4Projects and the data is contained in a different sheet, called Timeline.

However, I keep getting an error message to say there is a problem with the formula. how this formula needs to be entered in Series Values box?

View 9 Replies View Related

Modify Formula That Calculates Time?

Jun 6, 2014

I have the following formula:

=TEXT(D2-C2-TIME(,60,),"[h] h
s: m mi
s")

It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?

View 13 Replies View Related

Modify Formula To Dynamic Range?

May 29, 2013

I need to use the valve in the last row in column 27 and subtract the value in column "Y". Looking to modify this "R37C27" to dynamic range

Range("AG1:AG" & lr).FormulaR1C1 = "=IF(R[2]C[-13]=""Nanner Table"",R37C27-RC[-8],RC[-6])"

View 1 Replies View Related

Macro - Merge & Modify Formula

Dec 17, 2009

I use the merge button a lot for a group of cells in a column. However then I have to edit the formula in the merged cells.

I'm hoping there is a macro that can be made to bring these together.

Here's an example of what I do:

select range C2:C4. Cell C2 contains the formula =sum(B2)
merge these cells.

edit formula in new merged cell to be =sum(B2:B4)

Can I group this process into one macro?

View 9 Replies View Related

Increment/Modify A Formula Using Macros

Jul 22, 2006

I am needing a macro to modify a cell reference contained in a formula. For example: I have "=B2" in a cell of my worksheet, and I need some code to select and increment that by 1 so it becomes "=B3". I know how to use the ActiveCell.Offset command, as shown below, to select a specific cell so that it can then be modified, but I can't figure out how to make the actual change. ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Select

View 3 Replies View Related

How To Modify Source Data When Using VLookup Formula

Dec 17, 2013

I have 2 sheets, lets say SheetA1 and SheetA2. I am using Vlookup to serial number & getting data from SheetA1 to SheetA2. But I want to modifty the data. If i modify the data in SheetA2 that modification should relect in SheetA1. Is it possible...

View 5 Replies View Related

Modify Formula Containing Consecutive, IF Statements To Enable Drag-down

Oct 8, 2008

I can drag it down to fill a series of cells within a column.

Let's say the formula in cell A1 is:

View 5 Replies View Related

Excel 2002 :: Modify Formula To Merge Numerical With Text

Apr 21, 2012

I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").

=IF(AND(DJ10="HH"),COUNTIF(DF10:DJ10,"HH"),"-")

I use excel 2002.

View 2 Replies View Related

Modify The Formula Entries In The Following Code To Cycle Or Loop Through All Checks Required

Feb 22, 2009

I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.

View 2 Replies View Related

How To Add Formula To Existing Macro

Feb 21, 2009

I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.

View 10 Replies View Related

Expand On Existing Sumproduct Formula

Feb 26, 2014

Still finding the SUMPRODUCT function a bit challenging to fully grasp.

This formula works great to sum the miles (Q16:Q38) the team (A16:A38) drove in a particular month (B16:B38)

=SUMPRODUCT(--ISNUMBER(MATCH(A16:A38,INDIRECT(E3),0)),--(MONTH(B16:B38)=E7),Q16:Q38)

How would I expand it to check yet another column say (S16:S38) for the term " Charge Call" ?

So I can see the miles the team drove in March for all the charge calls.

Where can I go to study and learn more about SUMPRODUCT. Seems like just what I need to build interactive type dashboard reports.

View 2 Replies View Related

Copy An Existing Formula Down Or To Create One For The Same Job

Apr 2, 2009

I have a very simple lookup formula (eg: ='data'!C1) that I need to copy down about 4000 rows. The difficulty is that I need to miss out 5 rows in between each paste but no matter what I try when copying it down, it throws itself out of numerical order. I have tried to type the formula manually for every row but its taking forever and is bound to go wrong at some point. I have attached a very small example to show what I mean - Imagin this example needing the formula to run down in order up to row 4000?

View 2 Replies View Related

Add Another Variable Into Existing SUM (COUNTIFS) Formula

Jul 23, 2013

I currently have 6 formula (see below) that I want to add another variable for. The formulae add together letters in B1:B15 cross referenced against numbers in A1:A15. The first formula adds together the A's and O's in B1:B15 and cross references them with with cells beginning with 5 or 6 in the same row, A1:A15 down.

=SUM(COUNTIFS(B$1:B$15,"A",A$1:A$15,{"5";"6"}&"*")+COUNTIFS(B$1:B$15,"O",A$1:A$15,{"5";"6"}&"*"))
=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,{"5";"6"}&"*"))
=SUM(COUNTIFS(B$1:B$15,{"A";"O"},A$1:A$15,"4*"))=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,"4*"))
=SUM(COUNTIFS(B$1:B$15,"A",A$1:A$15,{"1";"2";"3"}&"*")+COUNTIFS(B$1:B$15,"O",A$1:A$15,{"1";"2";"3"}&"*"))
=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,{"1";"2";"3"}&"*"))

I want to cross reference each of these with a Gender variable (denoted as M or F, for male & female) in the C1:C15 range. Using the last formula as an example should tell me how many M's or F's in the range C1:C15 had B and began with either 1,2 or 3.

View 1 Replies View Related

Add IF Statement Into Middle Of Existing Formula?

Jan 23, 2014

I have lots of data in the form of names, and i want to extract all rows of data for that name. So if name "john smith" appears in column A 7 times, i retreice 7 rows of data.

My formula is this...

=IF(ISERROR(INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

This works perfectly and it gives me my 7 rows of data if i type in "john Smith" into A10, however, i want one more condition to be met in column C, whereby Column C is made up of either 1, or 0. If its a 0 i am not interested, if it is a 1, i am.

View 2 Replies View Related

Inserting Values In Existing Formula

Mar 17, 2009

I need to replace the values in the braces with the values in Column A. Is there an easy way to do this or am I stuck with doing each one manually? There are 92 lines in Column A.....

View 9 Replies View Related

Modify Existing Macro To Copy To Different Workbook Instead Of Open Workbook?

Dec 7, 2012

Here's my macro:

Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select

[Code] .....

Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.

View 2 Replies View Related

Copy Existing Row And Insert Values And Formula Into New Row?

Sep 1, 2013

I am trying to write a macro that should be able to copy existing row and insert values and formula into a new row just below the row. As an example below:

A
B
C
D
E

001
ADESF
500.00
1001210
EMF

The values in column C and D for the new row will be the same.

View 1 Replies View Related

Adding A Not To Exceed Condition To Existing Formula?

Mar 10, 2014

I'm preparing Quarterly Evaluations, and am having an issue because an Analyst has superseded an overall percentage goal. In the attached example, it is the overall evaluation with the 'problem data' in red D19 and highlighted.

I'd like to add a condition that limits the points in D19 for 'responsibility 3' to 150.

=IF(ISERROR(C19/B19)*(B10),"-",(C19/B19)*(B10))

*just a note for clarification, the other two responsibilities are a 'per hour' weekly goal, whereas the 3rd is an overall personal effort divided from the overall team effort.

View 4 Replies View Related

Continuously Adding New Cell To Existing Formula?

Jun 24, 2012

I'm currently doing a new worksheet for work

Cell's A1: Commission Per Sale Cell B1 Running total of commission

I would like the running total to continuously incorporate all of the above commissions and this has to be in the column next to the new sales commission.

E.g.

Commission $5 $7 $9
Running Total $5 $12 $21

View 2 Replies View Related

Adding To Existing Formula If Cells Are Blank

Mar 21, 2013

I 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.

View 1 Replies View Related

Altering A Existing Formula For Converting Bond Prices

Mar 31, 2009

--------------------------------------------------------------------------------

Ok i have a formula that i use to convert bond prices, which are in 32nds to numbers that excel can understand. I use the formula:

=LEFT(F7,FIND("-",F7)-1)+SUBSTITUTE(RIGHT(F7,LEN(F7)-FIND("-",F7)),"+","")/IF(RIGHT(RIGHT(F7,LEN(F7)-FIND("-",F7)),1)="+",64,32)

This allows me to type 101-16 in and excel recognizes this as 101.5

My problem is that my data I am copying into the sheet is often in 1/8ths. So the price will come in as 101-272, which means 101-27 and 2/8 (or 1/4).

I admittedly dont understand this formula and just use. Can someone help me tweak it to recognize the third digit?

View 11 Replies View Related

Macro To Apply Existing Formula To All Rows In A Sheet

Jan 26, 2009

I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.

These are the instructions I'm currently sending:

In cell n1 enter:
full phone number

in cell m2 enter:
=RIGHT("00000000" & J2,8)

Drag m2 to last record

In cell n2 enter:
=CONCATENATE(61,I2,M2)

Drag n2 to last record

In cell o1 enter:
Date of call

in cell o2 enter:
=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))

View 6 Replies View Related

Apply Named Cell From Another Worksheet To Existing Formula?

Sep 12, 2009

I am trying to apply named cell from another worksheet to existing formula.

For example I have a formula in sheet2 which looks like =sheet1!A1+sheet1!B1. I then subsequently name sheet1!A1 = Jan and sheet1!B1 = Feb. Thinking that if I apply these names the formula in sheet2 would look like =Jan+Feb. However I get the message "Microsoft Office Excel cannot find any references to replace" when I try to apply the names using Excel menu "Insert - Name - Apply".

View 4 Replies View Related

Difficulty Adding Minimum And Maximum Values To Existing Formula?

Mar 12, 2014

I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)

This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved