I need a formula that can provide all possible alphabet combination in another cell .
example
COLUMN A A1 = RED
COLUMN B B1:B50 = BELOW COMBINATION / RESULT
RED
ERD
DRE
EDR
DER
RDE
I'm tying myself in knots when trying to combine logical formulas to return a text value based on multiple cell values.
Cell A1 can equal 1-10, and cell B1 can also contain the values 1-10.
I want C1 to display the text "YC" if B1 value is equal to 9 or 10. I want C1 to display the text "SC" if B1 value is 5-8, and A1 value is 4-10. Any other combination of values should return a blank cell.
I tried splitting this into two components before trying to make it fit into a single equation, and i can achieve the first condition of display "YC" =IF(AND(A1>=9,A1
I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.
It ends up being 18 variations so my IF formula just won't compute.
This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:
IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc
Any way I can get this to work for all 18 possible outcomes??
I have set of people where by based on Level & designation want to find the percentile on their CTC.
Attached is the sample file. Percentile.xlsx‎
I want to create a formula that converts data covering three months (i.e. Jan, Feb, Mar) into fiscal quarters (=Q4), how can I do that?
In Column A, I have the project name, in Column B, I have the month (in date format) by which it needs to be completed, and I want Column C to automatically calculate the fiscal quarter for each project using the data in Column B.
I have a data array as follows..
Data1.png
I have a requirement as follows..
I need system to fetch values for student/subject score combination, which are in random order.
Req1.png
i've written the code below but somehow the output is not what i want.
i have a multiple records and each records contain 30 rows of info i have 3000 over records and they are all in one sheet. i need to find record using more than 1 criteria. I've written the code below. how to use VBA to search for particular strings in cells?
for e.g.
A1 : hello
A2 : world
A3 : anyhow
i want to find the cell that contains "yh" using VBA, and the output should be in cells A3.....
I need to create a formula that will allow me to use two sheets within the same workbook. I set up a dropdown box in my B2 cell in my first worksheet. My A4 cell is where I am trying to create my formula. I am not even sure how to say what I need. My drop down is filled with numbers from 1-211. I started my formula with =IF(B2=1,Sheet2!B3) this much I can get to work. It fills in the correct information. I do not know how to get it to continue. I need B2=2,Sheet2!B4.
View 12 Replies View RelatedI have a series of identification values that begin with text and end with a number. The trouble with sorting on this field only affects where the the first 9 entries end up since the number portion is not prefixed with a zero. Is there a way to sort these numbers so that ID2 (and not ID10) follows ID1?
ID1
ID2
ID3
.
.
.
ID9
ID10
ID11
I have created a table which is an index of all the worksheets. Now i would like to create a formula using the index reference. I get the right result if i concatenate but the text doesn't turn in to an excel formula.
=CONCATENATE("=";A1;"!";"D4") A1 contains the worksheet name, if the worsheetname is "A" then i would like it to return a formula that is =Sheet1!D4
I'm currently trying to make a sort macro that sorts a row of data that has a combination of numerical data and the text entry "N.A."
e.g.:
My field:
1
2
N.A
4
5
N.A
9
I want to sort the cells by descending value. But the result appears as:
My field:
N.A
N.A
9
5
4
2
1
But I want to numbers to appear first and have the "N.A."s to be after the number, since I have about 100 columns and some rows have like 30 N.A., which is pretty bad for usability if all that appears in the first screen are "N.A."
EDIT: by the way by ascending the data is fine since it orders by smallest number to largest and THEN the "N.A."
I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.
I'm struggling to do this in a Macro but I don't get it.
I guess this would work in a similar manner as Solver, but where Solver tweaks cell values to equal a given total, I've got a set of cells and I need to find the combination that equals an amount in another cell. Is there a way to do this?
View 7 Replies View RelatedI have this formula =TEXT(inventory!I2,"mmm-yyyy") which takes the date from another spreadsheet, but anytime that the date is missing, it susbstitutes for Jan-1900. Is there any way to revise the formula to leave the missing dates blank in the cell? For example, if the date is missing on the other worksheet, in this case, the inventory worksheet, is there any way for a blank to appear, instead of the Jan-1900? Also, anyway for this formula to spell out the month, and not just the abbreviation?
View 2 Replies View RelatedI am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View 9 Replies View RelatedMy Columns are as follows:
A1-Criminal Name, B1-Crime, C1-Age, D1-Ratings, E1-Punishment
In the punishment column one will get 22 years life imprisonment if he fulfills the following conditions.
1. He must have done Rape OR Murder
AND
2. His Age should be >30
AND
3. His Ratings should be>8
It should throw 22 years in the Punishment column only if the above conditions are met otherwise it should be Nil.
More Info on this:
1. Crime column includes Murder, Rape, Robbery, Assault, Kidnap etc
2. Age column ranging from 22-75 years.
3. Ratings column ranging from 1-10 points
4. There are 3400 records we have in the list
How to write an IF AND OR combination formula for this ?
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 a variable list of items in date order, there could be one, more than one, or, no items for any one date Each item has it's own line. I need a formula in a separate column (column F) to total each day's items. Column A includes the Date. Column F is a cumulative total (as in Column C) for items on the same date. Column C=D+E. I am looking for a formula which will add up items in column C if they have the same date in column A, and put the answer in the last cell in column F for that date. Example........................
View 5 Replies View Relatedhow to create a formula that creates an average but excludes the top 10% and bottom 10% values?
View 5 Replies View RelatedI am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:
Code:
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))
).
The answer should be 4,457.
Ticket Number
T20110819.0527
T20110830.0339
T20110901.0060
T20110901.0060
T20110907.0042
T20110907.0042
T20110908.0186
T20110908.0186
T20110908.0186
T20110908.0186
[code].....
Modifying my formula to work. I'm trying to have the value of Cell B2 based off the combination value of two other cells.
[Code].....
I have to calculate the following: PREV = VND * NSF * GROWTH. But there can more than one choice for the values:
-VND: analysis of the month value; if this is empty then analysis the average; if the average is blank then returns “No data”
-NSF: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1
-GROWTH: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1
You can find in the excel file attached the formulas that are possible to exist.
I am trying to make the "Next Phase up DATE" cell automatically configure accorfing to the Issue date and the PHASE 1, PHASE 2, PHASE 3 Selection.
Phase 1 last for 14 days
Phase 2 last for 21 days
Phase 3 has no phase up date
So if I put 1 July 09 in Issued Date cell and select Phase 1, the "Next phase up date will be 15 July 2009. If 1 July 09 in Issued Date cell and select Phase 2, the "Next Phase up date will be 22 jul 09. If 1 July 09 in Issued Date cell and select Phase 3, the "Next Phase up date cell will be (N/A or something to that nature.) The Doortag portions are refrenced to the upper portions, so I only need the formula for (merged)CDE10 & (merged)CDE32
I have a column say column A that holds text. If the values match say:
ABC1 or
ABC2 or
ABC3
And also match column B if it shows "Yes" then show "Correct" or "Not Correct".
I have the following text below and need a formula to extract the text
check deposit fee 0.020% per Month
mortage security $ 2.25 for every $1.00 borrowed
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
View 7 Replies View RelatedI have a workbook where the values in the worksheet "Bank Forecast" (including values that are added for eg =65005+25008+35000) needs cleared. Where there are formulas and text these must not be cleared.
I need to write VBA code that will do the above.
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC