Accounting For Multiple Possible Values In A IF(AND Statement
Dec 4, 2008
IF B1 has a possible value ranging from 1 - 5, and IF the value in E1 is equal or great than 2,5,10,10,15 BUT 2,5,10,10,15 need to match to specific ranges set in B1 1=2, 2=5, ,3=10 ,4=10 ,5=15 THEN IF TRUE "WITHIN" IF FALSE "NEEDS UPDATE"
Got help earlier with this formula: =IF(AND(B1=1,E1<=5),"WITHIN","NEEDS UPDATE") :D Worked Great! This was my attempt at expand that formula:
I would like to be able to search a group of cells for particular text, placing the true of false value in another cell, using this formula;
=IF(ISNUMBER(SEARCH("ACP",A2)),"ACP","XXX")
My reason for this is that I have a large amount of data that I need to break down. I have a column that is listed similar to below;
OR/ACP OM/ACT OR/MTS O/O
The part before the / is 'system code' The part after the / is 'module code'
I want to be able to have one additional column for system code and one additional column for module code, without having to manually go through all the data and make any amendments to it.
So as a result, (without the correct syntax) with 'S' being the system Column and 'M' being the module column, what I would like to achieve is;
If cell contains "O/" then S1 = Office but if cell contains "OR/" then S1 = Office Range but if cell contains "OM/" then S1 = Office Medium
I hope that makes sense.
Obviously repeated for the module column;
If cell contains "/ACP" then M1 = ACP but if cell contains "/ACT" then M1 = ACT but if cell contains "/O" then M1 = O
I need to see if it is possible to set up an IF statement that can pull information from cells based on the results already available in those cells.
ABCDEFGHIJKLM1Non FeaturedGoodGoodIdentical IMEIsGoodNeeds RLM NumGoodGoodGoodChk DOA BoxGood32Non FeaturedNo ExchangeGoodGoodGoodNeeds RLM NumGoodGoodGoodChk DOA BoxGood23
What I need is a formula in cell M1 that will check each cell B1 thru K1 for the data "Good". If it does not equal "Good", then I need the checks true/false value(s) to show the reason shown in the other cells.
In other words, for row 1, i need a formula where the outcome would be "Identical IMEIs" because D1 is the first box in the set that does not equal "Good". Then if D1 was corrected to "Good", the outcome would then be "Needs RLM Num".
I tried to do it in sections such as {=IF(B7"Good",B7, then the next check )} but the number of statements exceeds the number of IF statements that can be nested. And I really need the formula to fit in one cell.
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
I'm working on a sheet that will help us with lots of data. This spreadsheet has 2 work sheets in it, one is being used as a form, the other data.
I'm having trouble with a formula on a "Form" worksheet that tells me - if a cell on the "Data" worksheet is blank, then leave it blank. If it contains the word "ON", then put an "X" in the cell.
Here's what I am using to try and get this working, not having any luck. Any ideas?
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
I'm trying to automate the calculation of my prepayment schedule. The linked image http://img402.imageshack.us/img402/2...mentqueryp.jpg shows the basic layout of how it will look. The yellow cells are going to be the only input cells but i'm unsure what formula will achieve the desired result i'm looking for across the remainder of the spreadsheet. Essentially I need the formula to look at the period (start and end dates) the invoice covers and apportion it correctly. The apportionment isn't a straight equal division per month though as it has to be calculated according to what element of the expenditure hasn't been realised yet.
For example in the car park rent line the figure of £8,000 in July is derived from the fact that that is how much hasn't effectively been incurred yet as it relates to the remainder of the invoiced period (Aug - Mar) and is calculated as 8/12 x £12000.
I hope you understand what i'm requesting, if not, let me know and i'll try to provide a better explanation.
I want to use an IF statement that returns 3 values. I can do it to return 2.
Example: I am measuring performance of individuals. If they deliver below 50% I can return the value "Needs Improvement". If they deliver Over 60%, I need to show "Excellent" and finally if they deliver between 50% & 60% I need to return the value "Good".
How would I write a IF statement comparing 3 values. For example the field that it would be referencing would have values cell E51(1,2,3,4,5,6,7,...99). The pull back would be:
I have a list of 100 values in Column A of the attached spreadsheet Sample.xls. I need to count the number of values between a certain percentage including both positive and negative values. Example: I need to count all values that are between 0 and +5% and 0 to -5%.
I have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.
The issue is the format of the cell. In that attached worksheet, i inserted two formulas in cell b6 and b7. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell gets found.
My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. How I can find the zero value thats in an accounting format?
Below is a strip down version of the code.
VB: Sub test()[COLOR=#333333] [/COLOR] Dim rLookInADR As Range Dim foundcell As Range Set rLookInADR = Range("b1:b380") Set foundcell = rLookInADR.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole) MsgBox (foundcell.Row) End Sub [COLOR=#333333][/COLOR]
I just downloaded an income/expense template from Zillow to manage my rental properties. The template works great but it's only set up for 5 houses and I need more columns as I own more than 5. In the attached template the houses are listed by property codes and that is the column that I need to add to.
I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:
I would like to master the dreaded array formulas. Any Excel based accounting consolidation tool or other consolidation tool out there that I could adapt to consolidate group accounts on a monthly basis.
I am trying to create this macro for my accounting journal What I want to happen is that in my sheet1 if the 1st cell in column a is "CASH" then the whole row should be copied and pasted in sheet 2. i want this to happen from the first cell in a column until the very last data in column a which means i am not certain up to what row number it will have data since this is a journal with uncertain number of transactions.
I have the macro shown below, which I found in a 2003 issue of the journal of accountancy - and it works great. However, it only works on a data set that begins in cell A1. I want to incorporate it into a spreadsheet I have where my data set begins in cell E15 and goes down from there(column E will be the only column that this macro will need to run on and I need it to work on a data set that will vary in length). This macro performs a Benford analysis, which analyzes the first and second number of a data set.
Dim Arrayone(0 To 9) As Integer Dim Arraytwo(0 To 9) As Integer Dim Arraythree(0 To 9) As Integer Dim Arrayfour(0 To 9) As Integer Dim Arrayfive(0 To 9) As Integer Dim Arraysix(0 To 9) As Integer Dim Arrayseven(0 To 9) As Integer Dim Arrayeight(0 To 9) As Integer Dim Arraynine(0 To 9) As Integer Dim Arrayzero(0 To 9) As Integer Dim Arraytwotest(10 To 99) As Integer
Dim x, I
Dim Row As Long, Col As Long, Step As Long, Colcells
I am trying to determine if a specific date is what week in a 4-4-5 accounting month. See attached file. Is there a formula that can be used to do this???
I have a custom sort order that I use for sorting military ranks in a number of massive spreadsheets I am using. Normally the data we get has a long version for the ranks (column 1) and a rank code (column 2). When the rank code is there it is simple to sort by that column in descending order, but without it I need a custom sort. The problem is there are up to three equivalent ranks at each level across Army, Navy and Air Force as you can see in the attachment.
Is there a way I can set up a custom sort based on both columsn of data but sorting only on the second column. I could do vlookups but there are up to 50,000 rows and I don't really want to keep that formula there, and as I use it across multiple sheets (yes I know all one sheet, but it is segregated data and used in different situations) including a vba written value would have to occur for every row on every sheet - not the best option in my opinion - maybe it is.
I have a macro set up to copy and paste data from worksheets into worksheet 1. But the number of worksheets often changes. Is there a way to write this to include all worksheets even if the number changes?
Sometimes I will receive this file and there will be additional tabs. I just never know how many.
I copy down 500 rows knowing there will never be more data than that. And for each worksheet I paste to sheet 1 I add 500 rows to not paste over other data.
I would like the computer to read it as "select last indexed worksheet, copy paste to index 1, repeat while selecting the left adjacent worksheet until you reach index 1.
For each new worksheet selected add 500 rows to the last pasted amount."
If I would like to compute a running balance, how do I make the balance display conditional so that I don't have the balance displayed all the way down the page? I like to copy the formula and the formatting down the page, but I don't want to see the running balance unless I have entered info in the row.
I'm trying to run the code below to add functions to a couple of columns untill the value of the cell is equal to "End". I am recieving the error "Object variable or With block variable not set (Error 91)" and I'm not sure why. I have set my WITH up and I have defined the object. Any thoughts would be greatly appreciated. Thanks.
Option Explicit
Private Sub CommandButton1_Click() Dim shtXL As Excel.Worksheet Dim wbkXL As Excel.Workbook
Set shtXL = wbkXL.ActiveSheet With shtXL Do Until ActiveCell.Value = "End"