Multiple IF plus an AND and OR - is it possible?

[NICKRENN]

Hi

I feel like this is probably a bit must to ask but lets see - i have 3 pieces of data for Income / Debt / Credit.

For the purpose of this A1 = Income, B1 = Debt and C1 = Credit i need to create a % score based on 4 variable produced by those bits of data.

IF B1+C1 = A1 "0%"
IF B1 + C1 = 0 , "0%"
IF A1 = "BLANK", AND B1+C1 = > 0 , "100%"
If none of the above is true then,
B1+C1/A1 = %(which is just the formatting of the calculation)

If it in anyway possible to get all of that into a single formula? I just can't work out if its possible to embed enough functions to do that?

Thanks

Nick

 

[NICKRENN]

It worked if i put a "" around the ="0" - does that means its not reading it as a number ?

thanks

 

[AlanY]

That almost works! but its giving me #DIV/0!

An example of where that is happening is:

62 = 0, 65 = 4 and 68 = 2.

which should give me the 100% score from this part of the formula: IF(AND(VLOOKUP(A4,MSNA_Full!A:CJ,62)=0,VLOOKUP(A4,MSNA_Full!A:CJ,65)+VLOOKUP( A4,MSNA_Full!A:CJ,68)>0),100%

any idea why that isn't working? as its going to the final part of the formula and giving me the DIV/0! which makes sense.

thanks

for the actual example: the numeric values in 62/65/68 are as follows: 62 = 1, 65 = 5 and 68 = 4 so the answer should be 900% (i have the answers displayed as %)
but i'm getting a return of 100%.

Any idea?

Thanks

this shouldn't give you a divided by zero error, in fact I do get 900%

 

[NICKRENN]

this shouldn't give you a divided by zero error, in fact I do get 900%

yep for this i also got 900% but i was getting DIV/0 on another line that had the values 0 / 4 / 2 - it was only when i put "" around the 0 that it worked.