Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function?
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 returns more than 100%. I intend that the difference for more or for less, be presented in%. how to do? Thanks in advance Joćo -- Dipwind ------------------------------------------------------------------------ Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276 View this thread: http://www.excelforum.com/showthread...hreadid=571037 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function?
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" wrote in message ... 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 returns more than 100%. I intend that the difference for more or for less, be presented in%. how to do? Thanks in advance Joćo -- Dipwind ------------------------------------------------------------------------ Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276 View this thread: http://www.excelforum.com/showthread...hreadid=571037 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function?
Bob As you said the other way round, the result is 156%, but it is easier to work below the 100%. 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%. 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ćo -- Dipwind ------------------------------------------------------------------------ Dipwind's Profile: http://www.excelforum.com/member.php...o&userid=36276 View this thread: http://www.excelforum.com/showthread...hreadid=571037 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function?
Dipwind wrote:
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function?
Errata....
I wrote: Dipwind wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
?IF function for preventing a cell being filled by formula | Excel Worksheet Functions | |||
Last Saved Date Formula / Function | Excel Discussion (Misc queries) | |||
numerical integration | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |