Number Comparison And Logic Statement
Feb 8, 2007
I'm having a problem with coming up with a suitable excel formula. I have a table that calculates 2 values. Once these values are derived I need them to be compared to a separate table. This is how it works.
Table 1:
Cell B8 gives me total weight: 3187pounds
Cell C9 gives me the center of gravity in inches: 142.02
on sheet2
Table 2: Cell A2-A26 have weight increments every 50 pounds starting at 2200.
Cell B2-B26 have the minimum C.G. limit
Cell C2-C26 have the maximum C.G. limit
My question is how can I write a formula that takes the weight from B8 and compares it to the cells on sheet two (rowA) and finds the closest weight. Then from there finds out if my C.G. from C9 is within limits of my min and max values?
Sounds complex but it's not that bad. I'll upload the file to my website and let people download if they'd like to see it.
View 8 Replies
ADVERTISEMENT
Mar 31, 2014
Column A: T/F
Column B: True/False
Here's what I want in a sentence: If one of the cells in grouped row is "T", column B = True, if not then column B = False.
(row groups are separated by a blank row)
Example)
Col A,Col B
TTRUE
FTRUE
[Code] ....
I came up with following formula:
=IF(MATCH(TRUE,INDEX(ISBLANK(A1:A17),0),0)>MATCH("T",A1:A17,0),TRUE,"")
It checks to see if blank cell is found before "T" but it doesn't look rows above.
inincubus.grouprows-1.xlsx
View 3 Replies
View Related
Jan 28, 2010
my IF statement is breaking logic in picking the right strcode value.
View 2 Replies
View Related
Apr 28, 2014
I have a workbook that is titled "Results". It has 2 columns that I need, a 'status' column (B) and a 'finalized date' column (C)
Column B is the 'status' column: Formula: ('[May Database]Summary'!$B$1)
The May Database B1 column is a dropdown with options "In Progress" and "Final" that will be switched manually.
The results workbook pulls it's status from the May Database workbook.
So I need my results workbook to update it's 'finalized date' column (C) when the May database says "Final" but populate and save the day that the May database was manually switched to 'Final'
My question is would this formula work?:
Results workbook status column: C1= IF(B1="Final", Now(), "")
Would this save the date when the May Database was manually switched to 'Final' or will this just update my current day everytime I open this workbook since Now() returns current day? Is there a way to save a date when it was finalized and then keep that date?
View 1 Replies
View Related
Jul 17, 2013
I have a list of data for each year dating back 10 years and some of lists had entries that weren't available so the cell showed up #N/A. I searched and replaced them with 0 but when I graph the data it skews my graph. I was wondering if it was possible to take a logic (IF) statement that says if the cell = 0 then take the average of the next and previous data points so the graph looks smooth. i.e. =IF(G444=0,(G443+G445)/2,G444) This gives me the correct number i want, but i want to apply it to all my lists like a format if possible.
View 1 Replies
View Related
Jan 17, 2014
I'm trying to count the number of rows in a dataset where column A is something, B is something, C is something, D is not something, D is not something else, and column E > 0 OR F is something but I just can't get the result I want.
My actual dataset is a staggering 294,000 rows and 46 columns so here's a simplified version:
View 8 Replies
View Related
Apr 16, 2009
tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:
View 2 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Jun 11, 2014
How would I write an IF statement that would tell me if a number in one cell is more negative than another?
For Example,
Cell L23 has a number of -10mm
Cell M23 has -20mm
How would i write an if statement in another cell saying that if cell 23 goes beyond -20mm "warning".
I keep getting an error saying #NAME?
View 9 Replies
View Related
Apr 25, 2014
Data in B2 looks like this: "[13.0]DEV;[13.0]AUDIT" or can be "[13.0]DEV"
I need to wild card B2.
Although, not working.....my formula looks like this:
=if(and(A2="audit",B2"*"&"[13.0]AUDIT"&"*"),"Closed Audit",A2)
View 9 Replies
View Related
Aug 6, 2014
I have a medication start end and admission date.
and i want to assign a number value of 1 to all the records that the medication start date is 2 days after the admission date. How do I do this using the ifs function.
View 3 Replies
View Related
Jan 31, 2014
Is there a way to tell Excel to sum only up to a certain point? Like if A1 through AF equal 50, but I only want it to sum up to 40, to stop at 40?
Is there also a way to tell Excel using IF that if a statement evaluating a number is true or false, to return the value up until a certain number. For instance, if the value is 8 or less, then it should return that number, but if it exceeds beyond 8, say for instance 12, then if the value is 12, only return 8.
View 1 Replies
View Related
May 26, 2006
I have 4 and 5 digit numbers. For the 4 digit numbers, I want to be able to distinguish between the numbers by the last digit. For the 5 digit numbers, I want to be able to distinguish between the numbers by the last two digits.
Example: For 4 digit numbers, I would like to do something with all numbers ending in 1, 2, 3, 4, 5, 6, 7, and 8. For 5 digit numbers, I would like to do something with numbers ending in 10, 11, 12, 20, 21, etc.
The first step in being able to do this, I guess is distiguishing between 4 and 5 digit numbers, which I know can be done by the number lenght. However, the second part of looking at the last digit or the two last digits I don't know how to do.
View 4 Replies
View Related
Apr 18, 2014
I am getting this error and where th If not starts its is in red showing that is where the issue is:
HTML Code:
Sub RemoveRows()
Dim LR As Long, i As Long
Dim ws As Worksheet
Set ws = Worksheets("100 Airports")
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 10 Step -1
[Code] .....
View 3 Replies
View Related
Apr 8, 2009
I have a number of Grand Totals that equal to Hours of Work in a day ( Based on Demand from Customer Orders)
I only have 95 ( this will be a number in a cell that I want to be able to change if needed) work hours available to me each working day.
I want each day to attempt to fill in up to 95 hours , anything more and it will push the remaining balance forward into the other cells.
IE here is what I have for the next 5 days for Totals
211.3798120.304870.0099110.734375.041828.1292
Under the 211 I want it to change to 95 and then carry over the balance to the cell under 120 , I then want that cell to change to 95 and carry over its balance to the next cell and so on down the line. I will always have 22 Working Days I want to work with. So the last day may or may not have a greater then 95 total.
The 95 part I want to be able to change that to whatever number I think I will have available to me and it will adjust accordingly through the line.
View 10 Replies
View Related
Dec 26, 2008
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.
=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
View 5 Replies
View Related
Jan 25, 2007
There are three situations that should yield the answer "overdue" and a fourth and fifth situation that should yield an answer "on target". I'm new to functions and have spent SEVERAL hours on this
{=IF((G4<>0,F4<$g$1="Overdue")+(F4<G4="Overdue")+(G4<>0,F4>$g$1="On Target")+(F4>=G4="On Target"))}
View 10 Replies
View Related
Nov 6, 2011
Although the "Trip Sequence" is same , but I need to separate the Trips if the difference between the "1ST TIME " is more than 3 hours keep the first "Trip Sequence" unchanged , then by either adding a prefix or add 2000000 to the second "Trip Sequence" and 3000000 to the Third, so forth so on until all the rules are applied ( assuming more than 3 Trip Sequences could be also allocated in the database ).
Data Base
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA3270004
XXX05111835$VV1984GVACDG3270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
In the above examples , The "Trip Sequence" is same 3270004 but the "1ST TIME" is more than 3 hours difference:-
I want to be able ( ONLY IF "1ST TIME" is in access of 3 hours while the Trip Sequence are same , I want the results as follows:-
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA5270004
XXX05111835$VV1984GVACDG6270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
View 9 Replies
View Related
Apr 24, 2007
I have 3 ranges in an excel sheet
col 1 range contains a numeric value ("1" or "5")
col 2 range contains a string value ("left" or "right")
col 3 range contains a string value ("a" or "b" or "c")
example: I am trying to get a total count if col 1 contains either a "1" AND
col 2 value is "left" And col 3 value is "a". Note: If col 1 is blank (" ") then the rest of the row is irrelevant. See attachment
View 4 Replies
View Related
Dec 8, 2008
I am trying to calculate the number of forecasted hours worked per week for each contractor based on their allocation to the project and also based on a 37.5 hour standard week.
I am using a formula which was previously provided to me for another problem but theoretically should share the same logic. The formula works only when a contractor is forecasted to work for a full week. If there is a contract which finishes mid week the formula returns a weird value. Also another problem is that if a contract is starting on the 12th of January, it still forecasts a full week for the week commencing the 7th of January. I have attached a spreadsheet for everyone's reference.
View 4 Replies
View Related
Jan 15, 2009
I want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
View 6 Replies
View Related
Aug 13, 2009
Sample spreadsheet attached to provide a visual of what I'm trying to accomplish.
Issue: Need to identify which userid's can be deployed and which userid's can't
Detail: In the attached spreadsheet, column A would list all userid's that the project team wants to deploy an application to. Let say for this example that they want to deploy Adobe Acrobat Pro v9 to replace Adobe Acrobat Pro v7. In my actual spreadsheet, there are 1307 userid's in Column A.
As a result of meetings, we have identified 11 Functional Teams that CANNOT have their users upgraded to Acrobat Pro v9. Using internal LDAP look up tools, I am able to generate a list of userid's for each of those 11 Functional Teams. The numbers vary. Again, in my actual spreadsheet so far, Functional Team #1 has 600 userid's, FT#2 has 2700 userID's, FT#3 has 6700 userid's, etc. I'm not done generating the userid's for each functional team but I know I need assistance once I'm done!! LOL!!
Using Column A, I'd like to see if that same userid exists anywhere in Columns B thru L. If it does, then that specific userid cannot be deployed. I would like that userid to be displayed in column N. And in Column O, I'd like to display the name or names of the functional team that this userid is a member of. It could be multiple teams. This will help me identify users that have multiple functional team memberships. Useful data for future deployments.
Using Column A, if a userid does not exist in Columns B thru L, then that userid can indeed be deployed. I'd like to get that userid displayed in Column M.
View 10 Replies
View Related
May 7, 2009
In the columns below B is just 95% of A. What I want to do in column C is to put the maximum value of B over the range that corresponds until the value in A is less than that. For example for my 1st run the value would be $1,125.68 because this is the maximum amount before you fall below that in column A, or in this case hits $1,106.40. I want to be able to perform this automatically down the line (expanding the range until the logic test is true) but can't figure out how to do this. The formula would basically be the max of the range in Column B is > Column A until this is not true, then return the max in Column B for entry in Column C. Then in Column D I would run an identical analysis but return the Row identifer for the dataset.
View 11 Replies
View Related
Sep 21, 2009
I need your help on the attached sample sheet. I have used the concatenate & indirect combination logic to get the desire comments as outcome , but my problem is that I have 300 rows & 30 sheets in a workbook with this logic , it is increasing the size of my file as I have a total of 30 sheets with same logic. I need it in every sheet. If there is any other alternative or solution.
View 14 Replies
View Related
Feb 17, 2014
I have a dump which looks like below sample:
3
3
2
2
The actual dump will have around 200 - 500 rows of data.From the above I need to manually group them which are similar. E.g. the data of (3, 3, 2 , 2) in row 1 & 2 are matching, so they will be group 1 & row 3 will be group 2, row 4 will be group 3 respectively.
The sum & sort did not work as sometimes the data with the same range are not in same order. simpler logic/VBA code if any, which will automatically put group numbers if the data in two rows are exact match.
E.g of the output below for the above data:
Group No.
1
3
3
2
2
View 6 Replies
View Related
Nov 26, 2008
I am trying to get my head around sumproduct. So I had a go at trying to use the following formula to add data in odd columns.
=SUMPRODUCT(ISODD(COLUMN(A1:J1))*(A1:J1))
The formula below works but I don't understand whats wrong with the one above. Can anyone please explain how I would use isodd to achieve this? What am I doing wrong?
=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0)*A1:J1)
View 9 Replies
View Related
Feb 19, 2014
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
7,7,K,A,8,7
K,6,7,7,A,9
To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
View 8 Replies
View Related
Jun 12, 2009
Compare 2 columns of data and have a new 3th column tell me the result of the query based on some conditional information. So, here's an example.
Column A = Programmer Assigned (Yes/No)
Column B = Project State (concept, plan, develop, qualify, rollout, etc)
I'd like to compare the values in A to B and have the formula tell me if the programmer should be assigned in that phase or not. So, in english.
If A = No, then look to see if B = "develop" or "qualify", if it does, populate C with "Update required". If it doesn't, populate with "Correct".
I hope I explained it well enough for you all to help. I originally looked into using conditional formatting to just format the cell differently if I needed to perform an action.
View 3 Replies
View Related
Oct 24, 2008
to compare the values of data on one sheet (sheet1) with that of another (sheet2) to see if there has been any changes between the sheets over the previous week. If a macro could go through my data on sheet1, compare cell-by-cell the data that's in sheet 2 and highlight in red font any cells that have different values that would be awesome.
The macro would need to leave the "fill color" of the cell as it was.
View 14 Replies
View Related
Mar 6, 2009
I have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction".
For example:
Spreadsheet 1:
Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G.
If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F
View 15 Replies
View Related
