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

 

[AlanY]

=if(or(B1+C1=A1,B1+C1=0),"0%",if(and(isblank(A1),B1+C1>=0),"100%",(B1+C1)/A1))

 

[Joe4]

=if(or(B1+C1=A1,B1+C1=0),"0%",if(and(isblank(A1),B1+C1>=0),"100%",(B1+C1)/A1))

Just note that will return a combination of numeric and text values (anything enclosed in double-quotes is treated as literal text).
If you want to return ALL numeric values, remove the double-quotes around "0%" and "100%", i.e.

[COLOR=#333333]=if(or(B1+C1=A1,B1+C1=0),0%,if(and(isblank(A1),B1+C1>=0),100%,(B1+C1)/A1))[/COLOR]

You will definitely want to do that is you intend to use these results in any other formulas or totals, averages, etc.

 

[NICKRENN]

Hi

Thanks for the formula, i originally used A1 B1 etc for ease, below is my interpretation of your formula but it is telling me i'm incorrect. can you check if it follows the logic and if i've closed everything off properly.

=IF(OR(VLOOKUP(A4,MSNA_Full!A:CF,63)+(VLOOKUP(A4,MSNA_Full!,A:CF,65))=(VLOOKUP(A4,MSNA_Full!A:CF,61)),(VLOOKUP(A4,MSNA_Full!A:CF,63))+(VLOOKUP(A4,MSNA_Full!,A:CF,65)=0)),"0%",IF(AND(ISBLANK(VLOOKUP(A4,MSNA_Full!A:CF,61))),(VLOOKUP(A4,MSNA_Full!A:CF,63)+(VLOOKUP(A4,MSNA_Full!,A:CF,65)>=0),"100%"),(VLOOKUP(A4,MSNA_Full!A:CF,63))+(VLOOKUP(A4,MSNA_Full!,A:CF,65))/(VLOOKUP(A4,MSNA_Full!A:CF,61))))

Thanks for the tip on removing the "" i'll do that once the formula is working.

 

[AlanY]

give this a try

=IF(OR(VLOOKUP(A4,MSNA_Full!A:CF,63)+VLOOKUP(A4,MSNA_Full!A:CF,65)=VLOOKUP(A4,MSNA_Full!A:CF,61),VLOOKUP(A4,MSNA_F ull!A:CF,63)+VLOOKUP(A4,MSNA_Full!A:CF,6)=0),0%,IF(AND(ISBLANK(VLOOKUP(A4,MSNA_Full!A:CF,61)),VLOOKUP(A4,MSNA_Full!A:CF,63)+VLOOKUP(A4,MSNA_Full!A:CF,65>=0),100%),(VLOOKUP(A4,MSNA_Full!A:CF,63)+VLOOKUP(A4,MSNA_Full!A:CF,65))/(VLOOKUP(A4,MSNA_Full!A:CF,61))))

 

[NICKRENN]

okay i've made a few tweeks but seem to still have some issues, i think its at the end of the formula.

this is where i currently am

=IF(OR(VLOOKUP(A4,MSNA_Full!A:CJ,64)+VLOOKUP(A4,MSNA_Full!A:CJ,66)=VLOOKUP(A4,MSNA_Full!A:CJ,62),VLOOKUP(A4,MSNA_Full!A:CJ,64)+VLOOKUP(A4,MSNA_Full!A:CJ,66)=0),0%,IF(AND(VLOOKUP(A4,MSNA_Full!A:CJ,62)=0,VLOOKUP(A4,MSNA_Full!A:CJ,64)+VLOOKUP(A4,MSNA_Full!A:CJ,66>=0)),100%,(VLOOKUP(A4,MSNA_Full!A:CJ,64)+VLOOKUP(A4,MSNA_Full!A:CJ,66)/(VLOOKUP(A4,MSNA_Full!A:CJ,62)))))

Any ideas?

Thanks

 

[AlanY]

you got an extra space here

=IF(OR(VLOOKUP(A4,MSNA_Full!A:CJ,64)+VLOOKUP(A4,MSNA_Full!A:CJ,66)=VLOOKUP(A4,MSNA_Full!A:CJ,62),VLOOKUP(A4,MSNA_Full![COLOR="#FF0000"]A: CJ[/COLOR],64)+VLOOKUP(A4,MSNA_Full!A:CJ,66)=0),0%,IF(AND(VLOOKUP(A4,MSNA_Full!A:CJ,62)=0,VLOOKUP(A4,MSNA_Full!A:CJ,64)+VLOOKUP( A4,MSNA_Full!A:CJ,66>=0)),100%,(VLOOKUP(A4,MSNA_Full!A:CJ,64)+VLOOKUP(A4,MSNA_Full!A:CJ,66)/(VLOOKUP(A4,MSNA_Full!A:CJ,62)))))

 

[NICKRENN]

Thanks:

I've got a final update: so basically most of the formula is now working when it looks like this:

=IF(OR(VLOOKUP(A4,MSNA_Full!A:CJ,65)+VLOOKUP(A4,MSNA_Full!A:CJ,68)=VLOOKUP(A4,MSNA_Full!A:CJ,62),VLOOKUP(A4,MSNA_Full!A:CJ,65)+VLOOKUP(A4,MSNA_Full!A:CJ,68)=0),0%,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%,(VLOOKUP(A4,MSNA_Full!A:CJ,65)+VLOOKUP(A4,MSNA_Full!A:CJ,68)/(VLOOKUP(A4,MSNA_Full!A:CJ,62)))))

BUT - i am only getting 0% or 100% as answers, so for some reason this section seems to be over riding the following section.

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

 

[AlanY]

one misplaced ) here

=IF(OR(VLOOKUP(A4,MSNA_Full!A:CJ,65)+VLOOKUP(A4,MSNA_Full!A:CJ,68)=VLOOKUP(A4,MSNA_Full!A:CJ,62),VLOOKUP(A4,MSNA_Full!A: CJ,65)+VLOOKUP(A4,MSNA_Full!A:CJ,68)=0),0%,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%,(VLOOKUP(A4,MSNA_Full!A:CJ,65)+VLOOKUP(A4,MSNA_Full!A:CJ,68)/(VLOOKUP(A4,MSNA_Full!A:CJ,62)))))

 

[NICKRENN]

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