Searching Logical Formula And Also Formula For Combinations?

Feb 4, 2014

2 formulas that I need for my work (related with linear recurrences; this is serious work). First, I would like to know if is it possible to write a formula (or a set of formulas) that allows to write in a first column, in the first cell, the number 1 and in the following m cells, m distinct numbers chosen in a set S of n numbers (n > m); in the second column again the number one, and another set of m distinct numbers chosen in the same set S (here the order of the elements matters which means that the set {1,2} can be written as (1,2) and (2,1)). And I would like to do this until all the choices of m (ordered) numbers chosen in the set S are exhausted. Example: S = {2,3,4} which means that n=3. Suppose m=2. Then the columns would be

111111
223344
342423

The second formula I'm searching is (I think) a logical formula. What I would like is a formula that checks if the numbers in a fixed number of consecutive cells of a column are distinct and also non-null. If that's the case the formula should assign TRUE and if (at least) two cells are equal or (at least) one is zero, it would assign FALSE.

View 2 Replies


ADVERTISEMENT

Logical Formula: Value If True/false To Be Another Formula, Not Just A Set Value

Sep 16, 2009

I want to use the If formula but want the value if true/false to be another formula, not just a set value. Here's what I came up with (obviously wrong, please excuse my lack of experience) =IF(D2>C2,"=(D2-C2)*5","=((60-C2)+D2)*5 ")

View 2 Replies View Related

Logical Formula For Date

Jun 22, 2014

In column N I have a list of dates. In column Z I want to display either "Yes" where the date in column N has already passed or "No" if the date in column N has not passed. The formula I'm using in column Z is =IF(N2<TODAY(),"Y","N") however it just gives me "No" on every row regardless of whether date is passed or not.

View 11 Replies View Related

Logical Formula: Check In Q24 Such That If (P24=#DIV/0!,0,P24)

Dec 16, 2008

I need a solution for this situtaion! Iam using a french version of excel2003!
I have #DIV/0! in P24 (i.e an error value) So I need to check in Q24 such that if (P24=#DIV/0!,0,P24). I need to have a logical formula for this one!

View 3 Replies View Related

Use Of Logical Formula From A List

Sep 14, 2009

How to find whether a particular cell is blank or is text in a given row/column?
To elaborate more, I have some data in a row:

A B C D E
15 N 16 17 18

I am using a below formula to find whether there is any text in the row

ISTEXT(row(A1:E1)) but I am getting result as False.

View 9 Replies View Related

Searching For 50 Combinations Of Something Out Of Hundreds

Aug 10, 2008

ive got table consisting of about 4000 entries. Below is a small example.

[url]

I want to be able to make a button through macro's, where when pressed, it shows only certain postcodes - the way i've been doing it so far is using a combination of If, And, and Or functions to return true or false if a postcode begins with either 'AH4, AH1' and has an order number beginning with 'W', for example. Then I'd record a macro where it shows the ones showing true, via data filter. The problem I've got is I need to search for about 50 different combinations of postcode, out of a list of maybe 300 types of postcode, and using my current IF,AND,OR functions, I simply run out of room in the formula bar! For example, I need find a postcode beginning with CB1-25 (i.e. CB1, CB2, CB3) etc..etc.. the only problem is if i use my current LEFT function, and i want to search for say, CB1, CB14, CB15, out of a list of 'CB1,CB14,CB15,CB16', it will still return a postcode beginning with CB16, due to the CB1.

In the link I've highlighted the postcodes I want to search for out of that small list, and hopefully any solution I'm giving here I could expand to up to 100 combinations out of 500 combinations of postcode, for example.

How do I get it to search for a massive combination of criteria?

View 14 Replies View Related

Multiple Outcomes From A Logical Formula

Sep 28, 2009

I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).

Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?

View 5 Replies View Related

Complex Logical/conditional Formula

Jun 12, 2006

I am working between two worksheets. In one worksheet I have a list of
account numbers. In the second worksheet I have the same account numbers in
a different order. What I am trying to do is, If the account number from
worksheet one is found in the column with the account numbers in worksheet
two, I want to display the matching title for the account number that is in
the same row but different column in worksheet two.

So essentially (if acct # from column x in wks 1= an acct in number in
column m from wks 2, display the corresponding title in the same row of the
matching acct # found in column m). Is this possible?

View 10 Replies View Related

Offset Formula - Logical Function Using OR

Jul 10, 2013

I have following formula that returns #value!

=IF(OR(OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D))="",OFFSET(LIVE_JNL,ROW(5:5),
COLUMN(C:D))=0,""),OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D)))

Where the value in the cell which is referenced is either blank or zero then blank must be returned, otherwise the value which is being referenced by the offset formula.

View 2 Replies View Related

Workdays Function & Logical IF OR Formula

Aug 8, 2007

what was wrong with this formula, an error message comes up saying VALUE, im trying to get the formula to say that - If 2 dates are different then subtract one from the other to get a number of working days, but if the dates are both the same then to -1 from the value as for some reason the value is coming back as 2 when it should be 0?

=IF(OR(M17>0,N17>0),NETWORKDAYS(M17,N17,holidays)+1+Q17,IF(OR(M17=N17),NETWORKDAYS(M17,N17,holidays)-1,0))

View 9 Replies View Related

Formula Structure For IF / THEN With Logical Test Value Ranges

Feb 19, 2014

I'm trying to create a formula that will determine the bonus (%) for sales made. Here's the table:

<10000 sales = 0% bonus
>=10000 sales but <=29999 sales = 5% bonus
>=30000 sales but <=to 44999 = 7% bonus
>50000 sales = 10% bonus

Since the sales and bonus figures are subject to change each year I'd like the formula to point to the numbers but that part seems easy if I can get the formula right.

View 3 Replies View Related

Set Range Of Cells For Searching Missing Combinations?

Apr 19, 2014

The following macro searches for missing combinations. This macro will search the complete list and will return any missing combination from "1, 2, 3, 4" to "7, 8, 9, 10".

I need to make some changes in this macro, so that it will search for missing combinations only within a specified range of cells (and not the whole list). For example (see excel file attached), I would like to place a search within range("G23:J183"), from combination "1, 2, 6, 9" to combination "4, 6, 8, 10". In this case, it should return only 9 missing combinations.

Attached File: Example Find Missing Combinations.xlsm‎

View 2 Replies View Related

Logical Formula With Output Based On The Text Input

Dec 2, 2009

Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.

View 4 Replies View Related

Combinations Using Formula

Dec 14, 2008

I've attached an Excel..Its a small 4X4 matrix with numbers filled inside...Next to the right is a Combinations Column..I want to know how can i use just forumla to optain 4digit combination based on the 4X4 matrix. Rule is only numbers from the 4X4 be used.

View 14 Replies View Related

Using Formula To Return All Possible Combinations Of Cells?

Feb 25, 2013

I am attempting to return all possible combinations of 2 columns.

The first column is on Active Customer list as follows:

Customer Number(could contain up to 20,000 records)
1
2
3
4
5

The second column is on Finelines and markups as follows:

Material(Could contain up to 15 records)
a
b
c
d
e

The formula is on Sheet 3 as follows:

=INDEX('Active Customer List'!$B$2:$B$50000,ROUNDUP(ROWS($1:1)/COUNTA('Finelines and Markups'!$A$2:$A$15),0))&INDEX('Finelines and Markups'!$A$2:$A$15,2-MOD(ROWS($1:1),3))

I have manipulated the formula but can only get it to return all the combinations for the first two materials.

View 5 Replies View Related

Formula Or Macro To Show All Number Combinations?

Nov 19, 2007

as an example i will use the national lottery. numbers 1 to 49 inclusive. i need a formula that will list all the possibile 6 number combinations not repeating any.

View 6 Replies View Related

Patternd Searching Formula

Aug 16, 2008

I am trying to find a way to count non occurances of a number in a array of numbers. I have a list that is being entered like 112233222 and i want to know if there has been a gap of 7 since a 1, 2 or a 3 has been last inputted and indentify that number which ever it may be. Once this has happened i need to keep monitoring the string for any other occurances that are entered after. So above It would alert me that 1 had not come up in sequence for 7 times, and then if the entry continued and it looked like this 1122332221121 then 3 would be identified as not occuring for 7 times.

View 9 Replies View Related

Any Way To Validate Formula Only Searching Front Of A Word?

Jul 18, 2012

Here is the current code I am using: =IF(A1="MIXED DBLs 1st Place",'Mixed Doubles'!B9,"")

What has happened is I am changing the Field Mixed DBLs to a field that could have multiple values, so I am no longer going to be able to do a test on a Hard-Coded Value.. So is there a way to only validate on the front of the word like just MIXED DBLS and nothing after that??

View 2 Replies View Related

Logical Function: Logical Test With Three Possible Results

Apr 8, 2009

I am trying to create a logical test with three possible results:

h2 <10.2 "needs improvement"
(if false) h2=10.2 "meets standard"
if false h2>10.2 "exceeds standard"

Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.

View 4 Replies View Related

Number Combinations :: 25 Random Numbers (5 Digit Combinations)

Dec 15, 2008

I have 25 random numbers and I would like to get a possible 5 digit combinations of these numbers. Can anybody help me with the possible formula?

View 9 Replies View Related

Number Combinations :: Between 7 To 15 (6 Digit Combinations)

Jun 30, 2008

What I am looking for is to select between 7 and 15 numbers in total, I want all the possible 6 digit combinations for this.

EG: if I choose 2,9,11,13,15,17&26, it would look something like this

2,9,11,13,15,17
2,9,11,13,15,26
9,11,13,15,17,26
And so on.

If I chose more numbers (10) 1,2,3,4,3,6,7,8,9,10 it would start something like this

1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,5,8
1,2,3,4,5,9
1,2,3,4,5,10
And so on.

Please remenber I would like to be able to secelt between 7 and 15 number and be given all the possible combinations.

I would like it to be in one sheet but if that can not be done on as many as it takes.

It would be good if I could just type the required number into A1,B1,C1 and so on and they just gave the combinations required.

View 9 Replies View Related

Formula For Deleting A Formula When The Formula Has Done Its Calculation

Oct 7, 2009

I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.

I've got a formula

=IF(COUNTA(A1)=1,TODAY(),"")

When something is typed in Cell A1 the cell with the formula will input todays date.

Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.

View 6 Replies View Related

VBA To Read Text Formula And Related Constants And Insert As Formula Into Cell

Jul 30, 2014

I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.

Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example

Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc

I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g

In cell B10: =a+b*A10+c*A10^2

I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.

View 2 Replies View Related

Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula

Dec 22, 2009

Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

View 9 Replies View Related

Add Additional Formula To Existing Formula To Exclude Weekends And Holidays?

Mar 27, 2014

I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.

View 2 Replies View Related

Concatenate Formula Nested Within IF Formula Is Not Producing Expected Results

Aug 24, 2014

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:
D5: Middle Name:
D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

[Code] .....

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

How do I remove the additional (.) in cases where there is no middle name.

Attached File : Email Generator.xlsx‎

View 3 Replies View Related

Vlookup Formula In VBA - Object Error (input A Formula Into A Cell)

Apr 23, 2009

I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.

I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6

View 3 Replies View Related

When Dragging Formula Down The Formula Skips X Number Of Cells

Jul 22, 2014

Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:

A1= "1"
A2= "2"
A3= "3"

Then I type in the simple formula in the cell
B1= "=A1"

and then drag down but it skips 3 cells so then

B5= "=A2"

B9= "=A3"

If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"

this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.

Something similar to this B1 "=A1(skip3cells)"

View 4 Replies View Related

Copy A Formula In A Cell And Then Paste Only The Text Of The Formula

Jul 16, 2007

I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.

When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.

View 10 Replies View Related

Combine Formula Which Reduce The No. Of Additoanl Formula's Columns

Apr 9, 2009

I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.

To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.

Is There any formula to show fail subject in Remarks column without creating 5 additional columns?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved