i tried to compare 2 different cells with text inside.Each cell contains "yes","no"," ", 6 possible values
For example A B C 1 YES NO 2 YES YES 3 YES " " 4 NO YES 5 NO NO 6 NO " " In column C, i want to enter the result f.e. if a1="yes" and b1="yes" do 1,if a2="yes" and b2="no" do 2 and so on There is a function for this?
I consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.
which is completed with CTRL+SHIFT+ENTER, and it works properly. However, I want to do the same for all lines following, where it will go to the next item matching that value. For instance: on my SALESMEN sheet, I have John Smith, Fred Johnson, and Mary Williams. Fred Johnson is considered inactive, which is represented in the SALESMEN sheet as an "X" in column H. Therefore, I want line 1 to show John Smith, and line 2 to show Mary Williams.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
I am trying to sum the error points of staff in their work done based on a specific time frame. For example,from 4.00pm to 4.30 pm as per the attached. I tried sum product but did not work. Is there an addition formula that I need to use when incorporating time ?
I am trying to calculate two things: first, a simple count of entries (non-blank) in Col-B corresponding to 'a' in col-C. Ex. for Col-C='c', the no of entries in Col-B is 2. Here I was trying SUMPRODUCT(--(Col-B range""),--(Col-C range="a")) with CTRL+SHIFT+ENTER. It doesn't work.
second, a more complex count without repetitions. Ex. for Col-C='b' and Col-B='A', the no of entries in Col-A is 1, because there are two 1's. If there would have been three 2's and four 3's the count would be just 2.
I am attempting to create a multi condition IF statement for work. Essentially what we have is a column dedicated to the date in which a piece of equipment is supposed to go into service.
What we want to do is make it so that if the current date is 40 days past the scheduled in service date, a status column displays the term "Verify".
Additionally, if the scheduled in service date is 365 days from the current day, a status column should display the term "Future".
If it is any day in between the two, it should display the term "Active"
I have two seperate lines of code, one that covers verify and active and the other that covers future and active but I can't combine the two so that I have one function. The code I compiled is included below, any help would be appreciated.
I am trying to figure a way -- preferably in a single- cell formula -- to calculate a conditional volume-weighted average of a series of associated prices and quantities located in two separate columns based on the date (or dates) the transactions occurred.
I can do a simple Vol. Weighted Average (VWA) of all the prices and quantities over the five-day period with the following formula: (I have created named ranges for the price column (A1:A30=price), the quantity column (B1:B30=quantity) and the date column (C1:C30=date):
Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet. 1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12. 2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
See attached sample workbook. Row 37 current has formulas between columns AQ and IP that return a value 2 or 4. Conditional formatting on these cells then gives them an appropriate colour.
Due to the large number of cells involved, I wanted to use a multi-cell array formula instead. I have attempted this on row 50 which should give the same results, but as you will see this is not happening.
I'm trying to add "yes" as per the attached spreadsheet, using forumla. Account codes are duplicated (sometimes more than once, although no shown on the example) each account code needs to have the same yes column.
I am using multi-result array formulas to reduce error checking when processing arrays of numbers. However I cannot get this to work with some of the functions as they use Max or other array aware functions and so ruturn the same result for each cell they are enetered into.
Example for A1=2,B1=4
=2*A1:B1
array enetered gives 4,8
However
=Max(2*A1:B1,5)
array enetered gives 8,8 rather than 5,8. I have tried creating my own version of a max function, but am having problems getting it to evaluate array functions.
What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..
and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.
and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..
I need formulas that can evaluate the table above and provide the information below. If there needs to be multiple steps/formulas, I'm okay with that.
Two sticking points, BOLD - be listed twice with the same teacher. I don't want them counted twice in the "Size 3" column. BOLD & italicized- same student could be in two different teachers' classes. They need to be counted under both.
I am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to.
************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12= ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]
I would like to write a formula for shipping cost in Excel 2003, for specific shipping weights. Each product is a different weight. The result is in a new column.
What formula would I use for this column? The figures below are hypothetical.
A1 is 4 lbs. Shipping is $6.00 A2 is 8 lbs. Shipping is $12.90 A3 is 10 lbs. Shipping is $16.00 A4 is 15 lbs. Shipping is $24.00
Formula for the column is ?
If A1 is greater than 1 lb. and less than 5 lbs. then shipping cost is $6.00 or A1 is greater than 7 lbs. and less than 10 lbs. then shipping cost is 12.90 or A1 is greater than 9 lbs. and less than 15 lbs.then shipping cost is $16.00 A1 is greater than 14 lbs. and less than 18 lbs. then shipping cost is $24.50
I have a long chain of formulas calculating the return on a particular investment in equipment. We'll call the inputs A, B, and C, and the output (total savings generated) Z. There are several set combinations of values for A, B, and C that I'm interested in generating a summary report for.
There are a number of steps in-between the inputs that I'm interested in, all of which are necessary to get to the output, but is there a good way to have excel chart inputs and outputs without filling out the intervening data?
I suppose the whole thing is essentially a multi-variable, multi-step data table.
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
I am creating a IF(... formula, here is what i have so far...
=IF(B3='Max' AND C3='Sell', D3-J17,0)
I have two drop down boxes, one at B3 and one at C3, i want to D3-J17, but only when B3 = Max and C3 = Sell, otherwise 0 (really instead of 0 im gonna add in more formula for the rest of the drop downs, but for arguments sake lets say 0), what do i do?
I have a column called Average and it have numbers from 0 to 100. I want to set a string on another column based on the value of Average. The condition is
< 50 is negative = 50 is neutral > 50 is positive
For example Column A Column B 45 Negative 50 Neutral 80 Positive
