how to put together a function that will provide for me 1 of 3 results, (0, 1 or 2). Example Attached.
What I would like to do is have a function to have the results for the "Pts" (D) Column be either 0, 1 or 2 based on the following.
PM (D)<1.84 (F:10) = 2
PM (D)<2.25 (E:10), >1.84 (F:10) = 1
PM (D)>2.25 (E:10) = 0
Currently i am using index and match in excel but however i am unable to get the result tt I want.. what i need is
Data to check with item master whether all 3 (article, color code and size) matches and if it doesn't match, which of the field is wrong. (eg: wrong color code, wrong size or article don't exist).
[Code] .......
Above is my item master
Data to check
[Code] ........
Example case: 1
Checking first row of data check
Article number: 2-50660147
Color code: 6
Size: M
Result: "Wrong size"
But however, it returns as wrong size as it only reads the first time the article number appear in the item master and does not recognize the second time the article number appears with the correct color and size.
Is it possible to record a macro or write VBA to apply conditional formatting to certain columns of cells where there are exceptions/conditions for the different types of conditional formatting? If possible, can those exceptions/conditions be based on a specific type of formula?
See my DATA EXAMPLE picture linked below. On that example, Columns K-Q need conditional formatting but manager wants row 38 to be blue based on the fact that it is using a =SUM() formula. This represents one "part" out of 75 that could be on a given sheet, each "part" has a different number of sub-parts that are used. So I can't just highlight columns K-Q because of the occasional SUM row that needs to be blue.
The conditional formatting that I am currently using in Columns K-Q only apply to K3:Q37 and not to row 38 at all. I currently have conditional formatting on columns K-N, P-Q where when '=ISNUMBER(xx)' returns TRUE is white and when it returns FALSE is light orange. Column O uses '=ISTEXT(xx)' for the same colors. Manager doesn't want to copy paste that formatting and wants it setup as a Macro/VBA.
DATA EXAMPLE - [URL]
RELEVANT INFORMATION
Columns K-Q need conditional formattingColumn K - Formula pasted in only on lines that require the Sales Price to show, will return a number value or error
(=VLOOKUP(B38,'SaleWS'!C:G,5,0).Column L - Formula pasted in every cell in column except L38 is '=IF(Hxx="G",IF(Exx"Description EX",VLOOKUP($Dxx&$Gxx,'PriceWS'!$D:$F,3,0),""),"")'.
L38 has a sum of all above valuesColumn M - Formula pasted in every cell in column except M38 is '=IF(ISNUMBER(Lxx),Ixx*Lxx,"")'.
M38 has a sum of all above valuesColumn N - Formula pasted in every cell in column except N38 is '=IF(Exx="Description EX",VLOOKUP(Dxx,'Material'!A:O,15,0),"")'.
N38 has a sum of all above valuesColumn O - No formulas here, just manual entryColumn P - Formula pasted in every cell in column except P38 is '=IF(H38="P",VLOOKUP($Dxx&$Oxx,'PressWS'!$A:$L,12,0),"")'.
P38 has a sum of all above valuesColumn Q - Formula pasted in every cell in column except Q38 is '=IF(H37="W",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),IF(Hxx="T",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),""))'.
Q38 has a sum of all above values.Rows where they are using SUM for all the rows above for that part need to be blue
I need to look at 2 different cells and have a 3rd cell provide a number based on the first two cells.
Column A: either 1 or 2 (2 results in +5)
Column B: either yes or no (yes results in a +5)
Column C: function would result in either 0, 5, or 10.
Hello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?
View 3 Replies View RelatedI have a table which details an applicable fee given a level of performance. However the levels of performance can be any value and the fee level table which i need to look up gives conditional ranges such as if (out performance is) >4.5% and <5.00% then applicable fee will be 0.7%. However my table is large enough that using a nested IF formula seems unfeasable. I've attached a sample of what i mean which explains it better.
View 2 Replies View RelatedI have a range A1:D10 containing formulae. I want the cells to change colour depending on the formula result. Conditional Formatting will do the job but only up to three colour so I need to use VBA because I have 6 colours. For example, if the result is 1, the colour changes to blue, if the result is 2, the colour changes to green, etc
I have VBA code which will change the cell colour if I input a value, but not if the cell is relying on formula result. The formulae results are dependent on cells throughout the workbook. The formulae results can be letters or numbers, whatever is easier.
i am trying to count some cells in a range that are coloured either Red, Amber or Green. the cell colours are defined using conditional formatting. each cell has different criteria for the conditional formatting, but the outcome will always be one of Red, Amber or Green.
i have used the code that is in the FAQ section (the '=ColorFunction' code) and this works fine if the cells are coloured manually. as soon as conditional formatting applies the colour, the formula seems to stop working (even after manually recalculating the worksheet). i have attached my spreadsheet - i am trying to solve the formulae in cells B21:B23.
I have a cell which I want to colour green if C6>9, yellow if O6>60 and red if BOTH C6 and O6 are above their respective numbers. The first two parts of this are obviously straight forward, but I cant work out how to get the third part of it to work.
View 2 Replies View RelatedI have a conditional format that does not seem to be working for me. Cell B2 has a drop down optionSelect, No, Yes); Cell B3 is supposed to be conditionally formatted to return the following results if the criteria is met:
If B2 is equal to No or Yes then colour should become Yellow
If B3 is >0 then colour should become Blue
The problem is when B3 is greater than 0 it does not change the cell colour to Blue.
B3 Conditional Format #1 is =AND(ISTEXT(B2),B2<>"Select")
turn background to yellow
B3 Conditional Format #2 is =AND(ISNUMBER(B3),B3<>0)
turn background to blue
See attached for spreadsheet with conditional formats
I have a report that shows when our company has shipments covered by inventory (although you are not seeing the conditional formatting in the table). Columns K:Q represent shipment dates and shipment amounts. Columns D:I represent inventory with Column J being the total inventory. The conditional formatting works to highlight the cells in K:O of when shipments are covered by inventory. I need to be able to return the date (Columns K:Q) for an item (Column B) when a shipment is not covered by inventory.
For example Row 154: all shipments are covered by the amount of inventory. A shipment of 3 on 04/24 is more than covered the 105 pieces of inventory. But for Row 170, the shipment for 480 pieces on 04/23/12 does not have enough inventory to cover.
The result I need to return for row 170 is that 480 pieces are due on 04/23/12 that are not covered.
What formula would I used to return that value?
Excel 2010ABCDEFGHIJKLMNOPQ1LineITEMNEXT CUSTOMER PART WIP INVENTORYSTAGINGFINISHEDINSPECTDCYTRAILERTOTALPast4/18/20124/19/20124/20/20124/23/20124/24/20124/25/2012154Tabber105290325AA05290325AA-DC000010501053155Tabber1070025AC-EXP070025AC-
[Code]....
Is it possible to filter results based on conditional formatting?
View 6 Replies View RelatedIn Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedI'm trying to do a simple conditional format where the date is highlighted in red if the difference between that date and Todays date is greater than 90 days.
I've done the formula as =(TODAY()>$B5)>90, which appears to work in the sheet and shows either True or False but when inputting that formula into the conditional format it highlights every single cells.
Are conditional formatting formulas different?
I'm using the following phrase out of a much longer expression to
demonstrate that the return value of the MID function seems to be nothing I
can understand.
This expression:
=MID("6/10x",1,FIND("x","6/10x)-1))
returns 6/10 as text which makes sense. However I was under the impression
that when text in Excel contains numeric characters, it can be interpreted
as a number. So then how does the following expression,
=MID("6/10x",1,FIND("x","6/10x)-1)*1
return the result 38878? Does it still think it's text?
I am looking for a formula that will yield the following: IFAH9>AI9 return the word Green, IFAH9=AI9 return the word Blue, IFAH9<AI9 return the word Yellow. If AI9 is blank stay blank.
View 3 Replies View RelatedI am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
I am wanting to have conditional formats so the cell fill in a range on sheet 1 will change to:
- red if the value of a cell on a seperate sheet (date) is greater than 18 months old
- yellow if the value of a cell on a seperate sheet is 'In progress'
- green if the value of a cell on a seperate sheet (date) is less than 18 months old
I am having trouble with some formulas displaying the results of the logical test as words.. For example.
=IF(E4>F4,100, IF(F4>E4, 200))
That works perfectly but I want "100" to be "Valid" and "200" to be "Invalid"
However...
=IF(E4>F4,Valid, IF(F4>E4, Invalid))
only results in the #NAME? error for me?
I am trying to sum the details from sheet2 in sheet3. I am currently using a plain formula to sum-up the values. However, create a indirect and vlookup function to sum the values.
In sheet3, I have to sum the values from sheet2 based on the contacts. Sheet1, sheet2 and Sheet3 are connected and any update(insert,delete.. etc) will be made in sheet 1 only. Sheet2 automatically updates based on sheet1. Similarly, Sheet3 should also update based on sheet1 and sum the values based on contacts from sheet2.
I have added the formula which I am currently using in the sheet.
I have a macro that loops through a column of cells to retrieve a price that is buried in a string. The cells in this column are formatted one of two ways. Either they are completely empty, or the following pattern of string is entered: "$181.27 ea. for 12 on 5/21/14" (the quotes are not part of the string entered). What I need my macro to do is pull out "$181.27" and then drop the dollar sign and then do some more math before inputting a result in a different cell, but the prices range from less than a dollar to over $100,000 so I need to use split instead of left. Should be simple, yes? Here is what I have:
[Code] ......
But I get an error 9: subscript out of range on the line that calculates PriceSplit whenever it encounters a blank cell.
I have a problem with VLOOKUP function and I am looking for a workaround.
SheetA I have a column of business names and another column of the category.
SheetB has a list of credit card transactions and a column of categories - The results obtained from incorrect results from SheetA...
I'm trying to have three different possible results from one formula based on one cell.
If A1 is greater than 10 I want B1 show 5, AND if A1 is less than -10 I want B1 to show -5, AND if A1 is between 9 and -9 I want B1 to show the value of A1/2.
I've never used logic functions in excel before.
I am forecasting some numbers and I wanted to see which function would be best suited for my task. I used the TREND function and then the FORECAST, I was expecting the same results but appeared to have slight differences.
View 2 Replies View RelatedUsing data of unknown origin in Excel I found Isblank was giving a FALSE result on some apparently blank cells while giving a TRUE result on others.
Even if I used TRIM and CLEAN functions on the offending data and pasted the resulting values back the the original locations, the Isblank result was still FALSE. Also, the font colour was not set to white or transparent.
However, if I selected the cell with the 'invisible' data, clicked in the Formula Bar and pressed Enter without entering any new data, the problem disappeared for that cell. The problem also disappeared if I selected the problem 'blank' cell and pressed delete.
Can anyone explain this 'invisible data' and tell me how I can detect it using a function or formula?
graphing trigonometric functions...
sin and cos seem to work easily, but i'm having trouble getting the tangent function to graph (look) correctly...?
I'm currently using this formula:
=INDEX(B4:B64,MATCH(I5,A4:A64,0))
If I5's value is displayed more than 1 time in Column A, I want the information in Column B to appear in the same cell the formula is in in Column J
How can I sort two columns of function results using the result value? Excel is trying to sort on the function in the cells rather than the results themselves.
View 9 Replies View RelatedThe attached workbook contains a cell with validation list selection and a look up function. The validation list selection works fine, but the look up function is coming up with the wrong results on several items. Is this a bug or am I doing something wrong?
View 2 Replies View RelatedI'm working on an invoice generator, and I want to add a "lookup by phone number" feature. I've got an invoice database with invoice info stored horizontally for past invoices. On a separate sheet, I want to be able to type in the phone number in range ("O11:Q11") and have a list of invoice numbers and dates matching that phone number appear in Columns R and S respectively.
Here is some workbook info:
Invoice generator is in 'phonelookup'
Invoice database is in 'invoice database' and the table is called CompInvoices
In CompInvoices, Invoice numbers are col1, dates are col2, and phone numbers are col7.