I have a student grade worksheet with this formula and it returns # DIV/0 which I need to solve the problem. I have tried many different ways and none seem to work. Con someone assist with this problem. Here is the formula:
= SUM(R13,R55,R97,R139,R181)/(COUNTIF(R13,">0")+(COUNTIF(R55,">0")+(COUNTIF(R97,">0")+(COUNTIF(R139,">0")+(COUNTIF(R181,">0"))))))
It's actually a sumif/countif that works fine if i sort my table, which i don't want to do.
Here's the original formula:
Code:
(SUMIF(H4:H35,"1",G4:G35)/COUNTIF(H4:H35,"1")*1+SUMIF(H4:H35,"2",G4:G35)
/COUNTIF(H4:H35,"2")*2+SUMIF(H4:H35,"3",G4:G35)
/COUNTIF(H4:H35,"3")*3+SUMIF(H4:H35,"4",G4:G35)
/COUNTIF(H4:H35,"4")*4)/10
I'm trying to apply the formula using some other filters, like if(C4:C35=O5 & I4:I35=M5).
I tried creating a SUMPRODUCT out of it but it was just as bad:
Code:
=(SUMPRODUCT((C4:C35=$O$5)*(I4:I35=$M$5)*(H4:H35=1)*(G4:G35))
/SUMPRODUCT((C4:C35=$O$5)*(H4:H35=1)*(I4:I35=$M$5)*1)*1+...
And also this with sum(if)
Code:
=(SUM(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35))))
/COUNT(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35)))))*1+...
Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.
I'm logging failed password attempts and need to find the latest time logged for a single user (when user has failed 5 times).
So the 5th attempt, I need that time
So the usual - Application.UserName in the next free cell in a range and time stamp in the next cell.
What I've tried is:
Say log range is A1:B500 and
A=Username, B=Time stamp
In column C I've nested the two together
(this has more code but not required to get this working)
In C1
Code:
=A1&" "&"live"&" "&B1
So this will bring back "Username live 23:12:12". Now, say there is 5 of these (all with different times)
How do I find the latest time for that user?
Code:
IF(COUNTIF("USERNAME LIVE"&"*")=5,[find the latest time in that list",""
Just to wind you up some more, other users name will be logged and muddled up so it's literally -
"Excel - if a user has failed 5 times then give me the time of the 5th attempt"
I've tried a mixture of COUNTIF, MAX, LARGE, TIMEVALUE(...)
I searched on this and didn't find what I was looking for. I want to count entries that have critieria I specify in two different ranges. Is countif the way to do this?
View 9 Replies View RelatedI just need it to be able to test two seperate conditions on two seperate columns but i cant work out how its done
for example, i have two columns on a sheet, one called type and one area, both are text, i need to count how many items of one type are in a particular area. so if i have two columns "area" and "type" i want a total count of how many rows contain area="north" and type="b"
Hi guys, hopefully you can shed some light onto this small problem I am having.
The current formula I'm trying to use is:
=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(F AIL,""))
This isn't producing the desired results.
What I am trying to do in words is this:
IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail....
I'm working on a formula that will determine the lowest number in a range.
The complication is that the range varies based on how many rows match a certain criteria.
The formula I have created works correctly, however, I need to have the range go up to 10, and because of the limit of 7 nested IF's, I have to stop at 6.
=IF(A2=A1,"",IF(COUNTIF($A:$A,A2)=1,G2,IF(COUNTIF($A:$A,A2)=2,MIN(G2:G3),IF(COUNTIF($A:$A,A2)=3,MIN(G2:G4),IF(COUNTIF($A:$A,A2)=4,MI N(G2:G5),IF(COUNTIF($A:$A,A2)=5,MIN(G2:G6),IF(COUNTIF($A:$A,A2)=6,MIN(G2:G7),"")))))))
After a result is returned from this formula, I have to do the almost exact same formula, based on the min value from these results.
I am entering the following Nested IF(AND statement into a cell and getting a message stating "You've entered too many arguments for this function." Any idea on what formula I can use to get the needed information into this cell based on the fact that there are different inputs that can be entered into the related cell that will trigger the needed respons in the working cell?
=IF(AND(K1051="Down",L1051<2),"Yes","",IF(AND(K1051="Partially Down",L1051<2),"Yes",""))
I am trying to produce a report of supplier transactions sorted by area code. I have a spreadsheet of data consisting of Supplier Code, Supplier Name, Transaction Amount and Area Code.
I want to be able to firstly seperate the transactions by area, then also consolidate the data so it shows one row per supplier with total amount spent on that supplier and a transaction count on that supplier. I know how to sumif the transaction total and countif the transaction count.
However I have problems consolidating the suppliers in to one row per unique supplier and I also have problems nesting an AND statement in to the sumif/countif statements. I need additional criteria in the forumula to only count transactions in a specified area code.
I've attached an example spreadsheet to make it alot clearer. See results sheet in this workbook.
I am trying to track metrics for a QC dept. Right now my sheet has 35 lines of data and a header row, for 36 lines total. This is my funtion that isn't quite right:
View 2 Replies View RelatedI am struck in using IF /AND in nested function as follows,
A1B1C1D1E1F1G1
1 3 5 7 9 32
=IF(AND(F2>G2,AND(IF(A2>1,A2,AND(IF(C2>5,C2,AND(IF(E2>9,E2),""))
(Above, A1 to >G1 are cell address , in exact rows are actual values)
I am receiving an error message for a nested if statement. Excel is telling me my statement contains too many variables.
I am using a VLOOKUP to look up data in a table based upon a result in the first table, however, the entry that is returned is different based upon a unique cell value.
Would anyone have any suggestions on how to shorten this down?
=IF(CR="AG",IF(ISBLANK(A7),"",IF(ISNA(VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))),
IF(CR="G",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))),
IF(CR="MG",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))),
IF(CR="C",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,6,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,6,FALSE)))),
IF(CR="UC",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,7,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,7,FALSE)))))))
I am using VBA to put a formula into a cell. The formula is
VB: = IFERROR(VLOOKUP(LEFT(A5,11), 'New Quarterly Report'!A$11:E$37,3,FALSE), " - ")
But when I try to put this into a cell using the value option, I get a run time error 13: Type Mismatch. No problems while im compiling it but it comes up only during execution
VB: Sheets("Summation Table Template").range("B4").Value = "= IFERROR(VLOOKUP(LEFT(A5,11),'New Quarterly Report'!A$11:E$37,3,FALSE), " - ")"
When I try to put only the VLOOKUP, it works fine.
VB: Sheets("Summation Table Template").range("B4").Value = "= VLOOKUP(LEFT(A5,11),'New Quarterly Report'!A$11:E$37,3,FALSE)"
But I want the cell to not show #N/A when a value through vlookup is not found. Other formulas Iv tried are to use ISERROR with IF and also to store the formaula in a string and then put it into Range.Value but it still gives me a run time error 13.
I have a Userform which has 2 textboxes. User enter his employee id on textbox1 then enters a valid break time code which are 1,2 and 3 on textbox2. If User typed 1 then first breaktime start time will be pasted on the worksheet. When User returns after break he then enters his employee id again and then type 1 to end his first breaktime, the end time will be pasted on the worksheet. My program works fine however, I am trying to place a code which will prompt the user to end his first break time before entering another valid break code. Kindly check my code below as I am receiving an error message. 'Run-time error 9' Subscript out of range. This thing is driving me nuts. Any advise or can you tweak my coding vba experts.
View 14 Replies View RelatedUsing Excel 2010.
I extracted data from .pdf to Excel using Able2Extract. Now I need to scrub the output a bit.
I see commonalities in the data for the start and stop of each set of data that I can key in on.
Once I find the start and stop points for each set of data I would like to fill all rows in-between the points and then discard anything that remains outside of these boundaries.
I have something wrong in my logic, way too many rows are deleted.
Code:
Option Explicit
Sub GetLineSets()
'Purpose: Identify relevant line sets, delete all other rows
Dim wbBook As Workbook
Dim wsData As Worksheet
Dim strFormula As String
Dim lngRows As Long
Dim C As Range
Dim blnFlag As Boolean
[Code] ......
Is there a way to count cells that show up with the "Not Available Error", or even better, is there a formula that will count all cells in range range, except those with the "Not Available Error".
View 5 Replies View RelatedI am trying to do a countif in another workbook.
I enter my formula:
=COUNTIF('path[filename]tab'!$AH$11:$AH$300,"yes")
So this formula works fine when I have the other workbook open, but the next day when I open up the file (with the formula in it) and I click "update" button on the "this workbook contains links to other data sources" popup I get a #VALUE! error in the cell. Does anyone know how to correct this?
As soon as I open up the linked workbook the error goes away, but I didn't think this is how its supposed to function. I thought you didn't have to open up any linked documents. All the documents are on my desktop, so its not a share drive issue.
To test it out, I tried doing just a simple link to cell A1 in the other workbook and that works fine, so I'm thinking its the COUNTIF function that causing the problem.
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've been testing the countif function and for some reason it isn't working for me.
I have the following text in cells A1:A3
"apple"
"banana"
"peach"
I have the following text in cells C1:C3
"apricot"
"banana"
"grape"
I put the following formula in B1 and copied down into B2 and B3.
=COUNTIF($C$1:$C$3,A1)
It is meant to count how many of each fruit in A1:A3 is also in C1:C3.
The banana is the only one that is in both lists so the formula in B2 should return 1.
But all three formula's return zero.
If I change the range in the countif formula to relative it works. eg., =COUNTIF(C1:C3,A1)
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!!
Objective: 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
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 Related