calculation help

[Imran Azam]

Hi Guys

i have the table below with some results the score seems to be incorrect i am not sure if its my formula

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]James[/TD]
[TD]2%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Alex[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]score[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

the formula i use to get the result are as follow for both james and Alex

If(IFERROR(COUNTIFS('Data'!$AC:$AC,"James",' Data'!$V:$V,"Sold",'Win Rate Data'!$S:$S,">=01/05/2019",'Win Rate Data'!$S:$S,"<01/06/2019")/(COUNTIFS('Data'!'Data'!$AC:$AC,"James",'Data'!$V:$V,"Open")"-"))

because there was no data for alex it returns -

The formula i am using for the score is as follow

[TABLE="width: 500"]
<tbody>[TR]
[TD]=IF(B2>0.2,10,2)+IF(B3>0.1,10,0)[/TD]
[/TR]
</tbody>[/TABLE]

for some reason it is coming up with the total 20 rather than 10, the issue is it is recording 10 for alex result which is "-"

does anyone know why this is happening?

how can i fix this?

thank for any help

 

[earwig999]

It's recognising any character in the cell as ">0.1", so rather than "-" you would be better using "0" - Alternatively you could add a "" instead of "-" to show a blank instead of - or 0

 

[RoryA]

All text ("-*" in this case) is greater than any number, so you'll need to either test for "" separately before the numeric test, or actually return 0 but format the cell to show "-" instead of 0.

 

[Imran Azam]

Thank for you reply i dont want to show a 0 there i need it blank so i will try "" lets see if that works thanks for your help

 

[RoryA]

"" would still be treated as greater than any number. I suggest you just return 0 and format the cells to display "-" or whatever you need.

 

[Imran Azam]

when i try the "" i still have the same issue

All text ("-*" in this case) is greater than any number, so you'll need to either test for "" separately before the numeric test, or actually return 0 but format the cell to show "-" instead of 0.

 

[RoryA]

See my last post.

 

[Imran Azam]

thank you the only problem with this is that there are actual results whch are 0, this format will also change those to which i dont want

See my last post.

 

[RoryA]

Then you'll need a separate test like this:

=IF(B2="-",0,IF(B2>0.2,10,2))+IF(B3="-",0,IF(B3>0.1,10,0))