Ask Your Question
0

Returning a number when using the =IF(D3="100","25","0") [closed]

asked 2019-10-22 21:13:43 +0100

1meek1 gravatar image

updated 2019-10-22 21:15:33 +0100

I am using the IF function to return either 25 or 0 and or 100 or 0. These are percentages and for each one returned I need an average at the end. Each time I try to get the average I am getting Zero instead of an average regardless of what is in the cell range. =IF($'Daily Incentive Tracking'.D3="y","100","0") this is the first one I'm using and =IF(D3="100","25","0") is the second one.

In short I'm trying to use the answers to the IF function in an adjacent cell where those answers are averaged. See below for example of what I want to get


if if if avg
d3 e3 f3 g3
100 0 100 66.66

What I'm getting:
if if if avg
d3 e3 f3 g3
100 0 100 0

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by 1meek1
close date 2019-10-22 22:59:15.264090

2 Answers

Sort by » oldest newest most voted
0

answered 2019-10-22 22:04:03 +0100

m.a.riosv gravatar image

Is there any spacial reason to put the numbers inside quotes?, don't use the quotes and you can get true numbers, not numbers as text.

=IF($'Daily Incentive Tracking'.D3="y",100,0)
=IF(D3=100,25,0)

But if those numbers are percentages maybe could work better 100% or 1, 25% or 0.25.

edit flag offensive delete link more

Comments

You are a genius! Thank you! I removed the quotes and it works perfectly! I'm kicking myself for not figuring this out. lol

1meek1 gravatar image1meek1 ( 2019-10-22 22:57:29 +0100 )edit
0

answered 2019-10-22 22:09:34 +0100

Lupp gravatar image

The doublequotes you are using make TEXT of the sequence of digits placed between each pair of them.
The accumulating functions SUM(), AVERAGE() and some others ignore text whether or not its content (the string) looks like a number.
=IF(D3=100; 25; 0) should work as expected if the contents of D3 (and respectiv) are actually numbers.
(You need to care about the fundamentals if you want to make use of spreadsheets.)

BTW: A "percentage" should be seen as the value of a ratio (a rate e.g.). 25% is very different from 25, however. To regard it the same is wrong by a factor of 100.

edit flag offensive delete link more

Comments

(Sorry. My post crossed the one by @m.a.riosv .)

Lupp gravatar imageLupp ( 2019-10-22 22:10:50 +0100 )edit

Better two than none :)

m.a.riosv gravatar imagem.a.riosv ( 2019-10-22 22:24:12 +0100 )edit

You are a genius! Thank you! I removed the quotes and it works perfectly! I'm kicking myself for not figuring this out. lol

1meek1 gravatar image1meek1 ( 2019-10-22 22:58:14 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2019-10-22 21:13:43 +0100

Seen: 33 times

Last updated: Oct 22