Formula or Function?

[Dipwind]

I am trying to measure a service efficiency .

Cell C4, requested amount.
Cell D4, received amount.
Cell E4 =C4/D4. with result in%
Cell B1 = average Column E.

This works, but when the value in D4 is smaller than C4, E4 return
more than 100%. I intend that the difference for more or for less, b
presented in%.

how to do?

Thanks in advance
Joã

 

[Bob Phillips]

Surely the formula should be =D4/C4. Of course if you receive more than
requested, you get more than 100%, but that seems right to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dipwind]

Bob

As you said the other way round, the result is 156%, but it is easie
to work below the 100%.

the objective is to measure the efficiency against my request, sendin
the supplier more or less amount.

in the example that I gave the result it should be 64%, if the supplie
sends less amount than the request " C5=15000; D5=13000 " I obtain th
result in E5 of 115%. when actually the efficiency against my reques
was 85%.

being B1=average column "E" if I don't obtain the result of 85% in E5
I am distorting the result.

Thanks in advance

Joã

 

[joeu2004]

As you said the other way round, the result is 156%, but it is easier
to work below the 100%.

You can certainly always get a value that is no more than 100% simply
by doing the following:

=if(C5 < D5, C5/D5, D5/C5)

But that makes no sense to me, especially given the objective, to wit:

the objective is to measure the efficiency against my request, sending
the supplier more or less amount.

in the example that I gave the result it should be 64%, if the supplier
sends less amount than the request " C5=15000; D5=13000 " I obtain the
result in E5 of 115%. when actually the efficiency against my request
was 85%.

Which is exactly why Bob suggested that the "right" formula for your
objective is D5/C5.

But that formula will (correctly) return greater than 100% when D5
exceeds C5 -- that is, when what is received is greater than what is
requested, again as Bob said. Isn't that a correct measure of
efficiency "against the request"?

Bottom line: C5/D5 and D5/C5 are equally valid measures of
"efficiency". You just need to decide what you are measuring
efficiency against. C5/D5 tells you how much more (or less) the
request is over what is received. D5/C5 tells you how much more (or
less) the received is over what is requested.

In either case, I think it makes sense to use only one of those
formulas consistently. That means that sometimes the "efficiency
factor" will exceed 100%.

PS: Arguably, neither formula tells you "how much more (or less)".
You might consider subtracting 1 in both cases for that purpose -- for
example, D5/C5 - 1. In the example above, 13000 is 85% of 15000, but
it is 13% less than 15000.

 

[joeu2004]

Errata....

if the supplier sends less amount than the request
" C5=15000; D5=13000 " I obtain the result in E5 of 115%.
when actually the efficiency against my request was 85%.

[....]
In the example above, 13000 is 85% of 15000, but
it is 13% less than 15000.

I parroted the OP's mistake without thinking. Actually, 13000 is 87%
of 15000.