# Nested IF/And Condition Formula Assistance

Aug 4, 2009Current Formula

{=IF(AND(G144>0,F144:F173

Current Formula

{=IF(AND(G144>0,F144:F173

ADVERTISEMENT

I only grasp the basics of loops and not any experience w/ formulas in VBA to date.

For each item in col "I" run this formula (the cell references are relative)

=RIGHT(I6,LEN(I6)-FIND(" ",I6))

I am only at this point so far:

Sub ItemName()

Dim wb As Workbook

Dim ws As Worksheet

Dim i As Integer

Dim LRow As Long

Set ws = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")

LRow = ws.Cells(Rows.Count, 9).End(xlUp).Row

For i = 6 To LRow

=RIGHT(I6,LEN(I6)-FIND(" ",I6))

End Sub

The basic function of the loop is to Delete the first term of each cell in the range including the first space.

example:

Enjoi Barletta Deck

Resulting in:

Barletta Deck

I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula.

=IF(K23="","",IF(AND(K23=E23,ABS(B23-AVERAGE(F23:J23))>B20),B23-ABS(AVERAGE(F23:J23)-B20),

IF(AND(K23=F23,ABS(B23-AVERAGE(E23,G23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,G23,H23,I23,J23)-B20),

IF(AND(K23=G23,ABS(B23-AVERAGE(E23,F23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,H23,I23,J23)-B20),

IF(AND(K23=H23,ABS(B23-AVERAGE(E23,F23,G23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,I23,J23)-B20),

IF(AND(K23=I23,ABS(B23-AVERAGE(E23,F23,G23,H23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,H23,J23)-B20),

IF(AND(K23=J23,ABS(B23-AVERAGE(E23:I23))>B20),B23-ABS(AVERAGE(E23:I23)-B20),0))))))))

I 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 RelatedI am having trouble evaluating two conditions with nested logical operators to produce the output I need.

For example Condition 1 has 5 choices (A,B,C,D,E) and Condition 2 has 3 choices (a,b,c).

Breaking it down,

[Condition 1] option "A" always applies and should give "Yes" (No matter [condition 2], "A" always gives "Yes")

[Condition 2] option "c" always applies and should give "Yes" (No matter [condition 1], "c" always gives "Yes")

[Condition 1] option "D" never applies and should give "No" (No matter [condition 2], "D" always gives "No")

[Condition 1] options "C" and "B" only apply if [condition 2] is "b" (if so, the answer is "Yes")

What I need is the equivalent of:

if ((([condition 1] == A) || ([Condition 2] == c)) || (([condition 1] == B || C) && ([condition 2] == b)), "Yes", "No")

I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.

Issue: All I would like to do is for a NESTED IF Function to be able to say this:

1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square

2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.

3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.

If 'Column A' - 4" Square, Then 'Column B' - A

If 'Column A' - 5" Square, Then 'Column B' - B

& So on...

But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:

User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A

User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B

& So on...

Currently to do the above function, I have utilised the following Excel NESTED IF Function:

[Code] .....

& The following happens:

1. User inputs data into 'Column A'

2. The NESTED IF Function then provides an automatic response.

3. However it relies on the data being inputted without the ' " (Quotation Symbol)'

4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"

Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.

Example - IF Function.xlsx

=ROUND(SUMIF($C$26:$C$106,D126, $F$26:$F$106)/COUNTIF($C$26:$C$106, D126), 0). the formula is from a sheet which is used as like a questioning/ skills matrix.

The reference of d126 is a category eg: complaint handling and there might be 4 questions on complaint handling each of the answers being displayed as a number between the cells f26-106. If any of these individual cell results are equal to 0 or 1 then i want the overall answer for the formula to default to "training required" rather than the value it would give.

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:

D5: Middle Name:

D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

[Code] .....

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

How do I remove the additional (.) in cases where there is no middle name.

Attached File : Email Generator.xlsxâ€Ž

i was looking for some assistance in regards to a formula i have used for ranking. i have attached a sample spreadsheet of what i have done so far but struggling to work something so thot id try here.

(Sample WB) Currently i have managed to rank in accordance to column E but as i have alot of 0% i would like to be able to rank the 0% in accordance to the number of work done (column C).

For example i have 62 0% answers and Username Q has actioned 316 jobs with 0 Failures so should be ranked 1st as he has the highest number of jobs.

Is it possible if 0 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet A2 to A10 in work sheet Random Picks. And fill column D7 to D15 In sheet team matches with the contents of them cells.

If 1 cell is filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet B2 to B9 in work sheet Random Picks. And fill column D7 to D14 In sheet team matches with the contents of them cells.

If 2 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet C2 to C8 in work sheet Random Picks. And fill column D7 to D13 In sheet team matches with the contents of them cells.

If 3 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet D2 to D7 in work sheet Random Picks. And fill column D7 to D12 In sheet team matches with the contents of them cells.

If 4 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet E2 to E6 in work sheet Random Picks. And fill column D7 to D11 In sheet team matches with the contents of them cells.

If 5 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet F2 to F5 in work sheet Random Picks. And fill column D7 to D10 In sheet team matches with the contents of them cells.

If 6 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet G2 to G4 in work sheet Random Picks. And fill column D7 to D10 In sheet team matches with the contents of them cells.

The will eventually end in a series of pivottables. I am currently at the step where I need to add vlookups. This is for an inventory management report that averages 20,000 rows and 20 columns of data. I add a few columns (using vlookups) to increase visibilty to the operations dept.

The first thing I need help with is creating the VBA language for using a vlookup table from another workbook that pastes the vlookup formula every cell to the bottom of the data

Then I need to utilize an IF formula for one column that says IF c2 is blank use vlookup-formula1, and IF it is not blank use vlookup-formula2, and then paste at every cell all the way down.

I hope someone can help, and I hope I didn't write too much to where everyone will skip pver this thread.

I am currently to this state, and ready to continue.

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

Sub OHRTODDI1()

'

' OHR1 Macro

' Macro recorded 7/22/2009 by iahopbxm

'

' Keyboard Shortcut: Ctrl+o

'

' Sort and Add column Headers

Cells.Select

Selection.ColumnWidth = 9.43...........

I have a rather large formula that I put together and it's shown in column 'C' in the small sample file I have attached. I'd like to add one more criteria such that I avoid duplicate solutions in column 'C'. i.e. I only want the first occurrence of a group.

The formula works fine 'bucketing' the first entry. If I have an solution in C I want the formula to not give me another answer if the cell immediately above has an answer. In other words, rows 11 and 15 would be blank because rows 10 and 14 have an answer.

I've tried inserting another criteria in the AND statement, like ISBLANK for the cell above, but I can't get it to work. What am I doing wrong, OR is there a simpler solution?

=IF(C2="Windows",B2,IF((AND(A2=A3,C3="Windows")),B3,IF((AND(A3=A4,C4="Windows")),B4,IF((AND(A4=A5,C5="Windows")),B5,IF((AND(A5=A6,C6="Windows")),B6,IF((AND(A6=A7,C7="Windows")),B7,IF((AND(A7=A8,C8="Windows")),B8,"None")))))))Here is a link to the spreadsheet (D2 being the formula cell):

http://img199.imageshack.us/img199/8500/29072008.jpg

Basically, I am trying to put the columns data (if it has a porch, conservatory, windows) into one row for the contract number, as the dump which I have, is giving me seperate row's instead of one.

I am trying to get the formula to check if the contract number matches on the following row, and if it does, to check if the Product Description matches the header of that row (if it's a window, conservatory) if so, copy the quantity cell into D1, however, it's copying it even if the contract numbers do not match and I do not know where I am going wrong!!

I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).

I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2

>D2),(B2-C2)*F6+F2,IF((B2

the scenario is a spreadsheet for adding call centre operators evaluations (1 being worst and 5 being best) from the clients. in the spreadsheet, i need to create a function to add a quick evaluation based on the operators score from the client

so if the score is 4 or higher it would read "good - improving"

and if it is less than 4 "poor - improving"

i think this is just a simple IF question...but i have forgotten how to do it

Im doing a year nine maths project and need a little help with excel. This is my first time using macros/VBA. I have been playing around and have figured out a few things. I was asked to produce a game using excel, i am doing a kind of "racing" game with betting, i have the racing and most of the betting all working fine. I need to know how to get a message box to appear when the total of a cell reaches 0.

View 5 Replies View RelatedObjective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"

I have 2 worksheets.

Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)

Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg

ABC PTY LTD 5671

ABC PTY LTD 5683

ABC PTY LTD 5692

ABC PTY LTD 5610

ABC PTY LTD 5611

Tried the below formula

=if(C1='S2'!A2),=if('S1'!A2='S2'!B2),"ordered","")

I have a spreadsheet which contains dates (amongst other info) of when a specific form was completed for a client. We have 'date windows' which these should fall into. I'm really struggling with how to work this one out and whether to use nested IF's or try some kind of code (although my vb knowledge kind of stops after recording a macro). Bascially I need to know for each 'date window' if the window has been met or missed. There may be up to 30 different dates per person (in my example sheet I have up to 9).

View 2 Replies View RelatedHe works at state tax administration. He had this formula at work to calculate income tax.

View 3 Replies View RelatedI currently use 3 different formulas to return a value I want. The first one is =LEN to return a value. I then want to decuct 1 from this length value so I just use =A2-1 in the next cell. I then use =right so my returned result is always the X right most characters -1. Is there a way I can nest all these formulas so I'm getting the desired result in just one cell?

View 3 Replies View Relatedwhere the issue with the following formula is?

=IF(AND($E$2="N",$AA$550),"BT1",IF(AND(Z5="F",Y5>=0),"CLEAR",IF($AA$5

I have to write a formula which states the following:

if cells AA1,AB1 &AC1 = 0 then "Slow-Moving",

if of these cells AA1,AB1 &AC1 contains a number then "OK",

if cells, AA1,AB1,AC1,Z1,X1,Y1 all = 0 then "Non-Moving"

I believe an If and AND combination could work but its not working for me.

I have the following IF formula. It is working fine except the underlined part; it does not returned the value 15 ...

View 4 Replies View RelatedI have searched the threads high and low, but no one seems to have come across this issue: My formula works fine untill it comes across a blank in Comparison!E16: =IF(AND(Comparison!E14=Comparison!E15,Comparison!E15=Comparison!E16),"",IF(AND(Comparison!E14=Comparison!E15,Comparison!E16=""),"") IF(AND(Comparison!E14<>Comparison!E15),Comparison!E14))

I am comparing cells from a previous sheet onto Comparison so that I can see where the discrepancies lie.

I want to:

return nothing if all the cells match;

return nothing if the first two match, but the third is blank;

return the value if the first cell isn't matching

keeping in mind that this is only for the top cell: Comparison!14 - I will then have to bring it down to 15 and 16

I was wondering if someone could help me generate a Macro to do the following:

I have a sheet with the following characteristics.

Column A, rows 8-15 contain headers

Column A, rows 17-24 contain headers

Column A, rows 26-40 contain headers

Column B to CV, row 6 may or may not contain an "X"

Column B to CV, rows 8-15 & 17-24 & 26-40 may or may not contain the various data

I need a macro which does the following on the press of a button located somewhere on the sheet:

Generate a .TXT file in the folder C:Test with the name "Test_YYYY_MM_DD_HH_MM_SS_Full.txt" which uses the system time and date to fill in the values In the TXT file the following data should be created: ....

i try to lock cells in the area the VBA code affects. My goal is to actually have affected cells by this VBA code lock immediately after anything is entered in the affected cells. Area needing Debugging is in the If - Else portion. My worksheet will be protected.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LLoop As Integer

Dim LTargetRange1 As String

Dim LDestRange1 As String

LLoop = 10

While LLoop <= 1000

'Link column B to A

LTargetRange1 = "B" & CStr(LLoop)

LDestRange1 = "A" & CStr(LLoop)

If Not Intersect(Range(LTargetRange1), Target) Is Nothing Then

If Len(Range(LTargetRange1).Value) > 0 Then

Range(LDestRange1).Value = Date

Else

Range(LDestRange1).Value = Null

End If

End If

LLoop = LLoop + 1

Wend

End Sub

If the value in the Discount Y/N column is equal to Y, the formula should check if the value in the Tenure (Yrs) column is less than 5.

View 3 Replies View RelatedBut I've read that the new versions of excel allow for up to 64 nested if functions. I need to nest about 5 if functions, but after I write out the 3rd excel says there are too many arguments.

=IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="CPM", INDEX(vlookup!$E$2:$E$157,MATCH($F16,vlookup!$B$2:$B$157,0))*(J16/1000), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="CPA", INDEX(vlookup!$E$2:$E$157,MATCH($F16,vlookup!$B$2:$B$157,0))*(L16), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="dCPM", 0.04*(j3/1000))), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="dCPC", 0.008*k3, "calc")

Trying to make it even more complicated....probably again misplaced my parenthesis

=IF((AND('Input Page'!B43="-",OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker")),'Analysis Report'!D57*'Input Page'!B32/”2”,'Analysis Report'!D57*'Input Page'!B32/"4",IF(AND('Input Page'!B43="-",OR('Input Page'!B13="Renewal In-House",'Input Page'!B13="Renewal w/Co-Broker",’Input Page’!B13=”Expansion In-House”,Input Page’!B13=”Expansion w/Co-Broker),'Analysis Report'!D57*'Input Page'!B32,'Analysis Report'!D57*'Input Page'!B32/"2"))

I am trying to do a nested if formula but I can't get it to work.

The condition is: If value in cell R5 >0 then get the value in cel X29 on power 4.

=IF(R5>0,POWER(X29,4)

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