If Statement that might need a And or Or to complete

[Icehockey44]

Hi all,

hope you all had a great festive break ;0)

I have a contract based scenario that needs sorting.

If certain account numbers are entitled to receive either a full or half service and in some cases both. this all depends on the Account number and the warranty status.

I need a formula that works out if W,X,Y,Z account numbers that are in Warranty will recieve a Half, but only W and Y will recieve a Full.any other account number (not mentioned) recieve nothing.

AND

If account number Y has NO warranty the still recieve a Half, and W,X and Z are pending, any other account receives nothing. But account Y with NO warranty receive a Full, where as accounts W and Y with NO warranty will recieve a Full, X and Z are Pending. any other account numbers recieve nothing.

I have attached a spreadsheet with what i am after and how I would like it to look.

I have used the following formula to work out the basic entitlement as regards Half and Full entitlements based on Account number, but not sure how to add the Warranty bit in???

=IF(Cell=100001,"Y",IF(Cell=100002,"Y",IF(Cell=100004,"Y",IF(Cell=100005,"Y",""))))

i hope this makes a little bit of sense.

cheers

Donna

[Kevin UK]

Hi Icehockey44

I'm a bit lost, but try this in C2 & copy down, see if this is what you require for that column!
Perhaps it is me, but i don't understand the second part!

Please Login or Register  to view this content.

[bebo021999]

For half: 100004 is always "Y", 100003 is always blank, others if Y returns Y, otherwise Pending.

Please Login or Register  to view this content.

For full: 100001 and 4 alway return "Y", 100003 is always returns blank, others if Y return blank, otherwise Pending

Please Login or Register  to view this content.

Is it true?

[aydeegee]

I have a solution that uses no IF statements. Does this achieve what you need?

Regards

Alasair

[Icehockey44]

Hi Kevin,

this does work a treat for the first column, got anything for the 2nd column?

cheers

Donna

[Icehockey44]

Hi bebo,

Both of these worked jsut as needed, thankyou very much. and not too long as i have lots of other formula's in the sheet as well.

thanks for your time and help.

Cheers

Donna

[Teethless mama]

For full: 100001 and 4 alway return "Y", 100003 is always returns blank, others if Y return blank, otherwise Pending

Please Login or Register  to view this content.

Is it true?[/QUOTE]

You can't use syntax like this: =IF(B2={100001,100004},"Y","....."

Use it like this: =IF(OR(B2={100001,100004}),"Y",".....")

[Icehockey44]

Hi Alasair,


I didn't even think about doing an Index Match!!

this also worked a treat, although having the schedule table kind of complicates the look of the spreadsheet, but also makes additional contract changes easier to manage.

thanks very much for your time and help on this.

cheers

Donna

[Icehockey44]

Hi Alasair,

i have choosen your formula to use, due to its flexability.

I also have accoount numbers which are not the ones in the example, which currently come back as #n/a. Is there a way to amend the #n/a to a blank, as these accounts have no contract so receive no services?

Cheers

Donna

[aydeegee]

Hi Donna

Glad it works for you. The Schedule Table can always be relegated to another sheet to kee the main sheet looking pretty.

If you let me have examples of the additions, I will see if I can incorporate them.

Regards

Alastair

[Icehockey44]

Hi Alastair,

Not sure what's gone wrong with the formula on my actual spreadsheet, but where in the example have given you numbers starting in 1, mine are beginning with 4, so these need to be as requested on the above query.

when i have an account number that does not begining 4 (like a 2) I get #n/a's, but when I pop this number into your example it says Pending, but either way I need it to come back as a blank.

I hope this makes sense.

Thanks for your help

Donna

[aydeegee]

Doh! What did your teacher tell you, Alastair? Read the question!

I missed the point that all new contracts will be blank.

Just wrap the formula in an "iferror" statement, as attached.

Also - note that I have amended the missing 0 in the first of the matches

Regards

Alastair

[aydeegee]

Hi Donna

It seems our communications crossed.

If you will let me see the spreadsheet containing the problem, I will sort it for you.

Regards

Alastair

[Icehockey44]

Hi Alastair,

No this has solved the problem for me.

sorry not been in contact for the past couple of days, managed to contract a bug

But have now amended the code as suggested on you revised attachemnt and it works a treat.

thanks you very much for all of your help in this, but I fear it probably hasn't ended yet!!!

cheers

Donna

[aydeegee]

Glad to have been of help

Alastair