Hi,
I have quite a specific and in my mind complex, not only to write but explain.
I have a formula I want that counts the cells that have any information (I believe this to be a wildcard and uses "*") then use that number to multiply if that range of cells has an MC. But this needs to be relative across the 4 cells.
The premise is,
1 cell with content, MC = -300
2 cells, each MC = -150
3 cells, each MC = -100
4 cells, each MC = -75
The Points Awarded collumn already has this formula, which alson needs to be included:
=IF(G5=1,F5+1000,IF(AND(G5=2),F5*95%,IF(AND(G5=3),F5*90%,IF(AND(G5=4),F5*85%,IF(AND(G5=5),F5*80%,IF(AND(G5>5,G5<11),F5*70%,IF(AND(G5>10,G5<21),F5*60%,IF(AND(G5>20,G5<31),F5*40%,IF(AND(G5>30,G5<41),F5*20%,IF(AND(G5>40,G5<51),F5*10%,IF(AND(G5>50,G5<100),0,IF(G5="MC",-100,IF(G5="WD",0)))))))))))))
Thanks for any help
I have quite a specific and in my mind complex, not only to write but explain.
I have a formula I want that counts the cells that have any information (I believe this to be a wildcard and uses "*") then use that number to multiply if that range of cells has an MC. But this needs to be relative across the 4 cells.
The premise is,
1 cell with content, MC = -300
2 cells, each MC = -150
3 cells, each MC = -100
4 cells, each MC = -75
The Points Awarded collumn already has this formula, which alson needs to be included:
=IF(G5=1,F5+1000,IF(AND(G5=2),F5*95%,IF(AND(G5=3),F5*90%,IF(AND(G5=4),F5*85%,IF(AND(G5=5),F5*80%,IF(AND(G5>5,G5<11),F5*70%,IF(AND(G5>10,G5<21),F5*60%,IF(AND(G5>20,G5<31),F5*40%,IF(AND(G5>30,G5<41),F5*20%,IF(AND(G5>40,G5<51),F5*10%,IF(AND(G5>50,G5<100),0,IF(G5="MC",-100,IF(G5="WD",0)))))))))))))
Thanks for any help