IF Formula

[JackNeedsHelp]

Hey,

I'm trying to do an IF formula, but with quite a lot of IFs in there. Going through it step by step it makes sense to me, however I'm getting 'You've entered too many arguments for this function'. Does anyone know how I can get this to work?

Please Login or Register  to view this content.

Thanks,
Jack

[Glenn Kennedy]

Excel 2010 and later can handle 64 nested IFs. Are you saving this in a sheet with backwards compatability (earlier versions allowed 7 or 8 nested IFs).

[AliGW]

You need a lookup table to make what you are trying to do easier. Sorry, but I'm not going to try to work out by unpicking your formula what that is, so you'll need to tell us.

[JackNeedsHelp]

Excel 2010 and later can handle 64 nested IFs. Are you saving this in a sheet with backwards compatability (earlier versions allowed 7 or 8 nested IFs).

I've got the sheet saved as Macro Enabled, because another sheet has a macro in it?

[JackNeedsHelp]

You need a lookup table to make what you are trying to do easier. Sorry, but I'm not going to try to work out by unpicking your formula what that is, so you'll need to tell us.

Sorry, I don't quite get what you mean.

In terms of what i'm trying to achieve.. Column C will have a category name. So dependant on what category name it is, is which IF it should use. For example.

If C is 'Dresses' I want it to have the following conditions:

>=30 is poor.
>=70 is good.

However if C is 'Knitwear' I want it to have the conditions:

>=20 is poor.
>=40 is good.

Anything between these criteria I'd want to be 'middle'.

I hope this makes sense?

[AliGW]

So what I think you really mean is this:

Dresses

<=30 = poor
31-69 = middle
>=70 = good

Yes?

[JackNeedsHelp]

Sorry yeah, so it would be like:

<=30 = poor
31-69 = middle
>=70 = good

But only if column C says dresses, I need the sheet to do this rule but dependent on which of the 13 categories is in that cell.

[Glenn Kennedy]

Your parentheses were all over the place. I'm bored right now, so I unpicked them.

Please Login or Register  to view this content.

[JackNeedsHelp]

This has worked! Thank you!

Just for clarity what are the parentheses? Were my brackets in the wrong place?

[AliGW]

It may work, but it is not the most sensible way of doing things, as you have found out!

EDIT Brackets (braces in American) = parentheses.

[Glenn Kennedy]

Parentheses = brackets. They were in the correct place for Dresses (after Middle ground). thereafter, you only had only ONE "middle ground" and a million brackets right at the end. You needed a "middle ground" and a sprinkling (4) of brackets at the end of each clothing category and only 0.5 million brackets at the end.

[Glenn Kennedy]

It may work, but it is not the most sensible way of doing things, as you have found out!

EDIT Brackets (braces in American) = parentheses.

Like I said... I'm bored.

[JackNeedsHelp]

Hey,

Thank you again for this, I've got another IF on the sheet which isn't working. I've tried to break it down like you have but it's just not working out.

It needs to look for Product Group, in 'Category Lookup'. But if the Product Group in Category Lookup is Tops it needs to do two different lookups. This works, except unless it's Tops, Tops (Basic) or Tops (Bodysuits) it gives 'FALSE' instead of the lookup.

Please Login or Register  to view this content.

Has anyone got any ideas?

[JohnTopley]

You need the "xxxx" in the formula:

Please Login or Register  to view this content.

Post a sample file so we have some data to work with.

[JackNeedsHelp]

This has worked, thank you!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.