complex If then forumula

[jblaser]

Hi
I am trying to capture in an excel formula the follwing:

if A1+A2 =100% and A1 > 50%, allocate 10 point
if A1+A2= 100% and A2 > 50% allocate 9
if A1+A2>75%<100% and A1> 50% allocate 8 Points
if A1+ A2>75%<100% and A2> 50% allocate 7 points
if A1+ A2> 50%<75% and A1 > 50% allocate 6 points

(A1 and A2 are cell values expressed in %)

Grateful for any tipp you might be able to give me what type of formula I could use.

Best

JB

[Richard Buttrey]

Hi,

The rules aren't clear. What if A1+A2 = 100% and both A1 & A2 are both >50%. How do you decide between 9 or 10

Incidentally did you mean >=100% for the first two

[jblaser]

Dear Richard

Yes indeed. Incidentally I meant >=100%

Best

Jeremias

[samba_ravi]

=IF(A1>0.5,IF(A1+A2<0.75,6,IF(A1+A2<1,8,10)),IF(A2>0.5,IF(A1+A2<0.75,0,IF(A1+A2<1,7,9)),0))
Try this

[:) Sixthsense :)]

Try this...


=IF(SUM(A1,A2)>=100%,IF(A1>50%,10,IF(A2>50%,9,"-")),IF(SUM(A1,A2)>=75%,IF(A1>50%,8,IF(A2>50%,7,"-")),IF(SUM(A1,A2)>=50%,IF(A1>50%,7,IF(A2>50%,6,"")),"")))

[samba_ravi]

or =IF(A1>0.5,LOOKUP(A1+A2,{0,0.75,1},{6,8,10}),IF(A2>=0.5,LOOKUP(A1+A2,{0,0.75,1},{0,7,9}),0))

[Richard Buttrey]

Dear Richard

Yes indeed. Incidentally I meant >=100%

Best

Jeremias

And what about A1 & A2 both being greater than 50% and totalling 100% or more. Which result would you expect and why?
e.g. if A1 and A2 are both 60% then what??

[jblaser]

It doesn't seem to work but most likely because my question was not complete:
I have an indicator called: % of achieved outcomes. Outcomes can either be "fully achieved (FA)", "partially achieved (PA)" or "not achieved (NA)"
I would then like to allocate points according to the following rules:

10 points if FA+PA =100% and FA's share >= 50%
9 points if FA+ PA = 100% and PA's share > 50%
8 Points if FA + PA >=75%<100% and FA's share >= 50%
7 Points if FA + PA >=75%<100% and PA's share >= 50%
6 Points if FA + PA >= 50%<75% and FA's share >= 50%
5 Points if FA + PA >= 50%< 75% and PA's share >= 50%
4 Points if FA + PA >= 25% < 50% and FA's share >= 50%
3 Points if FA + PA >= 25%<50% and PA's share >= 50%
2 Points if FA + PA > 0%<25% and FA's share >= 50%
1 Point if FA + PA >0%<25% and PA's share > 50%
0 Point if NA = 100%

Grateful if you could help. Thanks again.

Best

Jeremias

[jblaser]

Anyone out there able to help? I'd really appreciate your support!

[samba_ravi]

pls attach sample excel file

[samba_ravi]

what about if both have 50% share on total

[jblaser]

Kopie von Kopie von Copie de Kobü Rating (2).xlsx

It is worksheet I4 "achieved outcomes"

Thanks for your help

[samba_ravi]

C46=IF(C45=100%,0,LOOKUP(C43+C44,{0,25,50,75,100}%,{1,3,5,7,9})+IF(C43>50%,1,0))
Try this

[samba_ravi]

or
=IF(C45=100%,0,LOOKUP(C43+C44,{0,25,50,75,100}%,{1,3,5,7,9})+IF(C43>50%,1,0))

[jblaser]

I still get an error message indicating that something is wrong with the formula

[samba_ravi]

See the attached file
i added iferror to your countif formula
and see the attached file
if it is not you are looking for what is your answer in this case

[jblaser]

It seems to work. Many thanks indeed!