More Excel Help

[Brand86]

Hi

Basically I need a formula that does the following, a nice little challenge for a genius:

Includes anything in "plan A" (column U)
Exclude anything in "Motability" (column T)
Include anything with a code of, either, "66666" or "77777" or "88888" or "99999" (in column K)
Checks if that item, if it passes all the other qualifications, is in a dealer list (currently using a Vlookup)

Got all the separate bits but can't for the life of me combine them. The bits I have are:

=IF(OR(AND(K2="66666"), AND(K2="77777"), AND(K2="88888"), AND(K2="99999")), "Qualify", "Not Qualify")

=IF(U3="plan A", "Qualify", "Not Qualify")

=IF(T4="Motability","Not Qualify", "Qualify")

(I made these on different rows hence the 2,3 then 4...)

Thanks for any help, this has stumped me!

 

[JoedotwasT5phy]

Look up nested if functions mate. Should explain how to do it

 

[iain187]

from what i am imagining, would a pivot table be suitable?

 

[iain187]

or this worked for me? (moved your data around in a test)

=IF(AND(OR(B1=66666, B1=77777, B1=88888, B1=99999), C1="plan A", D1="Motability"), "Qualify", "Not Qualify")

 

[Brand86]

No pivot tables are out- tried that and would need 16 to do what I need and makes the file massive, I am analysing the raw data itself.

Problem is I need to include some data, exclude motability, and also have the "or" bit for the code numbers...

Still not there so still a prize available for any winners!

Thanks Ian, saw your post - I need to exclude motability though, which is making things a bit trickier...?

 

[iain187]

oo I have made a mistake then, change D1="motability" to D1<>"Motability"

unless I am not understanding the problem correctly

 

[Brand86]

What does the <> function mean then? Not seen that before?

Thanks - your parenthisis seems to work, that's the bit I was struggling with!

 

[chris_182]

not equal to

 

[iain187]

it means 'not equal to' basically, sometimes in programming != is used, but excel seems to only accept <>

So basically it is saying C1 is plan A and D1 is not motability and B1 is any of your codes, the answer is TRUE (change to your cells where appropriate obviously!)

then the If statment is easy, when all that AND and OR jibberish means it answers TRUE then output qualify, if not, then output Not Quality.

 

[Brand86]

Genius - didn't work first of all, but put the code numbers as strings ("") and then worked! Brilliant!

The formula looks like this now:

=IF(AND(OR(K256="66666", K256="77777", K256="88888", K256="99999"), U256="A", T256<>"Motability"), D256, "Not Qualify")

Genius, I was excluding everything how I was doing it before, this method is a lot simpler now too!

Thanks guys!

 

[iain187]

odd, "66666" wouldnt work for me, I had to get rid of the apostrophes... i guess you must have the cell category set to something different than me. but meh, it works

 

[Brand86]

Yeh strange, but anyway, it works...now just have to work out how to do a vlookup for this against each dealers sheets!!

 

[iain187]

upload it if you get stuck, use something like sendspace.com !

 

[Brand86]

Basically, in the formula where I originally had "qualify" this now returns a dealer name if it's all true.

Then in a dealer sheet I just need it to count how many qualifying sales it has from the column in the other sheet where we have put this formula into...

So it just needs to match the dealer name really...any ideas?

 

[iain187]

how are you linking specific different dealers to a record that just says 'qualify', by the code? (66666 etc)

 

[Brand86]

Don't worry, used a countif function just matching the column against the dealer name (which is written in each dealer's page) then just produces a number, then next to that I have a simple if function so that once they achieve the minimum standard (of 100 this year) it says "Achieved".

I have had to turn off calculation for the whole spreadsheet now though, it is a 95 mb whopper!!!

 

[iain187]

I'm not exactly sure what is happening now, a screenshot maybe? I'm not exactly sure of the dealer and how you are going to get it to the other records we solved earlier.

 

[Brand86]

Sorry no Photobucket here but basically, all the raw data and analysis formulas are on the same page.

Then I also have a sheet for each dealer, with some drop down menus and also some stuff which is autopopulated, such as these orders. I just have a countif statement for each page matching each dealer's name (in cell b2 of each page) against the amount of time each dealer's name is returned in my column on the main formulas page, as in there remember I told it to return the name of the dealer if the formula was true?

It works anyway, as I did a filter for a few of the dealers and they all came up the same using the countif statements.

 

[iain187]

i kinda had a feeling a countif would be all you needed but i wasnt sure how you were linking qualifying records to dealers, are you sorted?

 

[Brand86]

Yeh think so cheers mate, all I need to do now is do some Get formulas...do these link to a cell so that if I were to add or delete a row, it would still be pointing to a specific cell though? As in my summary table it has dealers in rows so if I added or deleted one, don't want my formulas pointing to the wrong dealer if you know what I mean!

 

[iain187]

never seen =GET() before, I think if you name the range and then dont add to the bottom - insert a row in the middle, it should stay good?

 

[Brand86]

Yeh I mean where you just put "=" and then click on the cell you want it to point at...seems to work all good now anyways, thanks!