An IF formula who take account of the previous and next line of the first column.

**Maximus620** · 04-16-2015, 11:07 PM

HI,

Ok so i will try to be clear about i'm looking for.

In my excel file attached, there is some People who wants to do option. So if they do option 2 to 5 its ok. I they do the option 1 its not ok.

The option 1.5 is Not ok if its link to option 1
But ok if its link to option 2 to 5

So this option 1.5 is a problem for me ^^ because i need to create; if(or($a2=$a1,$a2=$a3),...) or something like that to be sure that Option 1.5 is always take into account.

I hope i have been clear

Thanks for your help

**FlameRetired** · 04-16-2015, 11:56 PM

What is there in the upload that tells us 1.5 is linked to any of those values? What "link" are you referring to?

**Maximus620** · 04-17-2015, 12:01 AM

I mean, if "Lo" do the option 1 and 1.5 That is not OK. But for "max who do option 2, 1.5 and 4 it shoule be written OK

**:) Sixthsense :)** · 04-17-2015, 12:01 AM

In C2 Cell

=IF(VALUE(TRIM(SUBSTITUTE(UPPER(B2),"OPTION","")))<2,"Not Ok","Ok")

Drag it down...

**FlameRetired** · 04-17-2015, 12:45 AM

sixthsense and I agree on all results except Max Option 1.5. This is an array formula.

Formula:

Please Login or Register  to view this content.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. Then drag down.

Edit Mistake in formula. I had originally used a helper column for the SUBSTITUTE.

**Maximus620** · 04-17-2015, 01:57 AM

Hi thanks

The thing is, in my normal table the number are replace by subject like Math, chemistry.. so i can t use a inferior or superior. If you see what i mean ?

**:) Sixthsense :)** · 04-17-2015, 02:00 AM

Show your actual data structure with expected result for giving exact solution

**Maximus620** · 04-17-2015, 03:10 AM

The table is more like that. Test ON.xlsx

**:) Sixthsense :)** · 04-17-2015, 03:18 AM

Originally Posted by Maximus620

The table is more like that. Attachment 389717

Please explain what needs to be done with the new set of data?

**humdingaling** · 04-17-2015, 03:22 AM

your original post looks nothing like your latest one
all the solutions posted would not work as it was based on original file......

what are you actually expecting as output?

if name appears more than 1 only make first one available ?
or if options appears more than x amount of times...only available for x times? or something else completely

**Maximus620** · 04-17-2015, 03:48 AM

Sorry,

Its look strange for sure. The student can do for example (chemistry, geometry and physics) AVAILABLE. "Lo" cannot do Maths and Geometry (NOT AVAILABLE). And greg cannot do just Math (NOT AVAILABLE).
All the other if they dont do math its AVAILABLE.

Well my table is a way bigger, with the family name of teachers and others so i can send the real one, sorry.

If you dont understand its ok " im not helping you a lot" in term of explanation.

**humdingaling** · 04-17-2015, 04:59 AM

total understand if you dont cant provide the full set of data or even part of it but you need to explain the fundamental logic of how answers are concluded

if you cant explain the logic to the answer you want
we cant replicate it and in turn give you the formula you are after

**Maximus620** · 04-19-2015, 09:59 PM

I hope your weekend was good

In this table I have few student who want to choose some option. But:

if its written math it is "not available"
if it is math+geometry it is "not available"
If it is any other option it is "available"
If it is any other option +geometry it is "available"

This is supposed to be all the criteria. I hope it is better now

Thanks for you time everyone.

**bebo021999** · 04-19-2015, 10:32 PM

So, if I interpret correctly, make it easy with this logic: If someone choose "Math", it writes "Not available" to all his chosen subjects?

Try:

=IF(COUNTIFS($A$2:$A$21,A2,$B$2:$B$21,"Math*"),"Not available","Available")

**Maximus620** · 04-20-2015, 03:02 AM

Yes, its works ! thank you.

But how did you get the geometry available or not available ?

I cant see it in the formula.

I just need to be sure that geometry will be always after math, right ?

**bebo021999** · 04-20-2015, 10:57 AM

Originally Posted by Maximus620

I cant see it in the formula.

I just need to be sure that geometry will be always after math, right ?

Arcording to this:

Originally Posted by Maximus620

if its written math it is "not available"
if it is math+geometry it is "not available"
If it is any other option it is "available"
If it is any other option +geometry it is "available"

Only "math" is not available, right?
Other options, including geometry, is available.
Therefore geo doesnot needed to show in the formula, it belongs to "FALSE" part of "IF"

**Maximus620** · 04-20-2015, 09:33 PM

Yes,

math is always not available. But Geometry is also "not available" when he is linked to math (when a student just ask for math and geometry. Otherwise all the other are available, included geometry with some other options.

**bebo021999** · 04-21-2015, 11:08 AM

Originally Posted by Maximus620

But Geometry is also "not available" when he is linked to math

Not only geo, but any other subject which is linked to math is "not available", that COUNTIFS did:
=IF(COUNTIFS($A$2:$A$21,A2,$B$2:$B$21,"Math*"),"Not available","Available")

**Maximus620** · 04-21-2015, 09:43 PM

Ok,

thank you very much, it wil be okay now i think.

An IF formula who take account of the previous and next line of the first column.

LinkBack

Thread Tools

Rate This Thread

Display

An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Re: An IF formula who take account of the previous and next line of the first column.

Thread Information

Users Browsing this Thread

Similar Threads

Looking for a formula that marks previous cells in line as done

[SOLVED] Remove an account range form a column with account numbers.

What is the formula for bank account row balance column

[SOLVED] Macro problem on, Yellowed line - previous line or next line.

repeat formulae taking into account previous column's info?

Bookmarks

Bookmarks

Posting Permissions