Less Excel more Math

T

Ty

This post is less about Excel and more about Math and percentages. I
have some numbers and trying to think. It's like I need some NZT like
the movie Limitless if you are reading this post and saw the movie.
Anyways...

I need 3% of a total number. Then I need to check to see if the
customer actually has 3 % in the actual number in another column(E2).
I have 120(F2) and I did a =+F2*0.03 which equals 4(in G2). Now the
customer just so happen to have 4 in column E2. So, now I need to
check to see if they are at 3%. So, I did a =+E2/F2(in i2). I have
that the customer is right at 3%(in i2).

Here is another example using the logic up top.

715(in F3) 21(in G3) and my answer for the percentage is 3% (in i3)

Is my logic ok?
 
J

joeu2004

I need 3% of a total number.  Then I need to check to see if the
customer actually has 3 % in the actual number in another column(E2).
I have 120(F2) and I did a =+F2*0.03 which equals 4(in G2).  Now the
customer just so happen to have 4 in column E2.  So, now I need to
check to see if they are at 3%.  So, I did a =+E2/F2(in i2).  I have
that the customer is right at 3%(in i2).

Here is another example using the logic up top.
715(in F3) 21(in G3) and my answer for the percentage is 3% (in i3)

Yes, your math is basically correct. =F2*3% gives you 3% of the value
in F2. And =E2/F2 computes percentage of E2 over F2. If F2 is 120
and E2 is 20, E2 is about 16.67%.

But first, be careful with testing for exact equality. Arithmetic
with non-integers and even ratios (division) of integers is rarely
exact. So you need decide if you want greater than or equal to 3%, or
if you want less than or equal to 3%. I will assume greater than or
equal.

Second, be careful about how you interpret numbers that Excel
displays. For example, 21/715 might look like 3% if you format it as
Percentage with zero decimal places, but it is actually less than 3%,
namely about 2.9371% -- and even that is not exact. Usually [*] the
display format does not alter the underlying value.

So if F2 is base number and E2 is the customer amount, you could
write:

=IF(E2/F2 >= 3%, "at least 3%", "less than 3%")

Or if I2 is =E2/F2, you could write:

=IF(I2 >= 3%, "at least 3%", "less than 3%")

Alternatively, if G2 is =F2*3%, you could write:

=IF(E2>=G2, "at least 3%", "less than 3%")

-----
Endnotes

[*] Re: "Usually the display format does not alter the underlying
value. Exception: when the calculation option "Precision as
displayed" (PAD) is set. I do not recommend it. If you choose to set
that option, be sure to make a copy of the Excel file first. PAD can
change constants irreversibly, which might break other calculations.

Second, you
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top