Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
percent of a fixed and variable number
here is my problem, I have three cells a1,b1,c1. a1: fixed, b1: variable, c1:
total of both as a %. I need to find the total precent the B1 maybe either (+) or a (-) number. if over 100% to indicate it as "100%", if between 100% and 0% to indicate it as is, and if under 0% to indicate it as a "0%". I am not even sure if its possibe. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
percent of a fixed and variable number
=median(0%,A1+B1,100%)
-- David Biddulph "Lorne" wrote in message ... here is my problem, I have three cells a1,b1,c1. a1: fixed, b1: variable, c1: total of both as a %. I need to find the total precent the B1 maybe either (+) or a (-) number. if over 100% to indicate it as "100%", if between 100% and 0% to indicate it as is, and if under 0% to indicate it as a "0%". I am not even sure if its possibe. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
percent of a fixed and variable number
wow, that is good, but missing what the actual % would be if between 100% and
0%. Sorry if i was not clear on that part. But I am so happy with this so far. Thank you "David Biddulph" wrote: =median(0%,A1+B1,100%) -- David Biddulph "Lorne" wrote in message ... here is my problem, I have three cells a1,b1,c1. a1: fixed, b1: variable, c1: total of both as a %. I need to find the total precent the B1 maybe either (+) or a (-) number. if over 100% to indicate it as "100%", if between 100% and 0% to indicate it as is, and if under 0% to indicate it as a "0%". I am not even sure if its possibe. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
percent of a fixed and variable number
I assumed from your question that you wanted A1+B1 if that is between 0% and
100%. If you want something else other than A1+B1, put that instead of A1+B1 in the formula. -- David Biddulph "Lorne" wrote in message ... wow, that is good, but missing what the actual % would be if between 100% and 0%. Sorry if i was not clear on that part. But I am so happy with this so far. Thank you "David Biddulph" wrote: =median(0%,A1+B1,100%) -- David Biddulph "Lorne" wrote in message ... here is my problem, I have three cells a1,b1,c1. a1: fixed, b1: variable, c1: total of both as a %. I need to find the total precent the B1 maybe either (+) or a (-) number. if over 100% to indicate it as "100%", if between 100% and 0% to indicate it as is, and if under 0% to indicate it as a "0%". I am not even sure if its possibe. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
percent of a fixed and variable number
what i am looking for is if the difference between b1 and a1 is = or greater
then 100% to show in c1 "100%" or if the difference between b1 and a1 is = or less then 0% show "0%" but if the difference between b1 and a1 is between 99% and 1% to show the actual %age calculated. keeping in mind that b1 could be greater <= a1 can even be a negitive which would I know show a 0%. Where ai is what we need B1 is what we have and c1 is QTY Needed QTY from Material % of TOTAL REQUIRED "David Biddulph" wrote: I assumed from your question that you wanted A1+B1 if that is between 0% and 100%. If you want something else other than A1+B1, put that instead of A1+B1 in the formula. -- David Biddulph "Lorne" wrote in message ... wow, that is good, but missing what the actual % would be if between 100% and 0%. Sorry if i was not clear on that part. But I am so happy with this so far. Thank you "David Biddulph" wrote: =median(0%,A1+B1,100%) -- David Biddulph "Lorne" wrote in message ... here is my problem, I have three cells a1,b1,c1. a1: fixed, b1: variable, c1: total of both as a %. I need to find the total precent the B1 maybe either (+) or a (-) number. if over 100% to indicate it as "100%", if between 100% and 0% to indicate it as is, and if under 0% to indicate it as a "0%". I am not even sure if its possibe. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
percent of a fixed and variable number
Still very unclear from your description. Are you wanting the difference
between B1 and A1, as a percentage of B1? If so, you want =median(0%,(A1-B1)/B1,100%) or =median(0%,A1/B1-1,100%). In either case, format the result as percentage. -- David Biddulph "Lorne" wrote in message ... what i am looking for is if the difference between b1 and a1 is = or greater then 100% to show in c1 "100%" or if the difference between b1 and a1 is = or less then 0% show "0%" but if the difference between b1 and a1 is between 99% and 1% to show the actual %age calculated. keeping in mind that b1 could be greater <= a1 can even be a negitive which would I know show a 0%. Where ai is what we need B1 is what we have and c1 is QTY Needed QTY from Material % of TOTAL REQUIRED "David Biddulph" wrote: I assumed from your question that you wanted A1+B1 if that is between 0% and 100%. If you want something else other than A1+B1, put that instead of A1+B1 in the formula. -- David Biddulph "Lorne" wrote in message ... wow, that is good, but missing what the actual % would be if between 100% and 0%. Sorry if i was not clear on that part. But I am so happy with this so far. Thank you "David Biddulph" wrote: =median(0%,A1+B1,100%) -- David Biddulph "Lorne" wrote in message ... here is my problem, I have three cells a1,b1,c1. a1: fixed, b1: variable, c1: total of both as a %. I need to find the total precent the B1 maybe either (+) or a (-) number. if over 100% to indicate it as "100%", if between 100% and 0% to indicate it as is, and if under 0% to indicate it as a "0%". I am not even sure if its possibe. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
percent of a fixed and variable number
I got it to work what i had to do was make another column and hide it
unformatted. IT all goes like this; A1: Fix numberor my {QTY Needed} B1: Variable number {QTY from Material Tracker} C1: =(B1-A1) {QTY remaining from Receiving} D1: =B1/A1 {Hidden} E1: =if(D1=1,"100%",if(D1<=0,0,D1)) {% of Total Required} {Headings}. It might be dirty but it works. Thanks for your help. "David Biddulph" wrote: Still very unclear from your description. Are you wanting the difference between B1 and A1, as a percentage of B1? If so, you want =median(0%,(A1-B1)/B1,100%) or =median(0%,A1/B1-1,100%). In either case, format the result as percentage. -- David Biddulph "Lorne" wrote in message ... what i am looking for is if the difference between b1 and a1 is = or greater then 100% to show in c1 "100%" or if the difference between b1 and a1 is = or less then 0% show "0%" but if the difference between b1 and a1 is between 99% and 1% to show the actual %age calculated. keeping in mind that b1 could be greater <= a1 can even be a negitive which would I know show a 0%. Where ai is what we need B1 is what we have and c1 is QTY Needed QTY from Material % of TOTAL REQUIRED "David Biddulph" wrote: I assumed from your question that you wanted A1+B1 if that is between 0% and 100%. If you want something else other than A1+B1, put that instead of A1+B1 in the formula. -- David Biddulph "Lorne" wrote in message ... wow, that is good, but missing what the actual % would be if between 100% and 0%. Sorry if i was not clear on that part. But I am so happy with this so far. Thank you "David Biddulph" wrote: =median(0%,A1+B1,100%) -- David Biddulph "Lorne" wrote in message ... here is my problem, I have three cells a1,b1,c1. a1: fixed, b1: variable, c1: total of both as a %. I need to find the total precent the B1 maybe either (+) or a (-) number. if over 100% to indicate it as "100%", if between 100% and 0% to indicate it as is, and if under 0% to indicate it as a "0%". I am not even sure if its possibe. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
percent of a fixed and variable number
If you wanted B1-A1 as a percentage of A1, why not
=median(0%,(B1-A1)/A1,100%) or =median(0%,B1/A1-1,100%)? Why bother with 2 unnecessary extra columns? -- David Biddulph "Lorne" wrote in message ... I got it to work what i had to do was make another column and hide it unformatted. IT all goes like this; A1: Fix numberor my {QTY Needed} B1: Variable number {QTY from Material Tracker} C1: =(B1-A1) {QTY remaining from Receiving} D1: =B1/A1 {Hidden} E1: =if(D1=1,"100%",if(D1<=0,0,D1)) {% of Total Required} {Headings}. It might be dirty but it works. Thanks for your help. "David Biddulph" wrote: Still very unclear from your description. Are you wanting the difference between B1 and A1, as a percentage of B1? If so, you want =median(0%,(A1-B1)/B1,100%) or =median(0%,A1/B1-1,100%). In either case, format the result as percentage. -- David Biddulph "Lorne" wrote in message ... what i am looking for is if the difference between b1 and a1 is = or greater then 100% to show in c1 "100%" or if the difference between b1 and a1 is = or less then 0% show "0%" but if the difference between b1 and a1 is between 99% and 1% to show the actual %age calculated. keeping in mind that b1 could be greater <= a1 can even be a negitive which would I know show a 0%. Where ai is what we need B1 is what we have and c1 is QTY Needed QTY from Material % of TOTAL REQUIRED "David Biddulph" wrote: I assumed from your question that you wanted A1+B1 if that is between 0% and 100%. If you want something else other than A1+B1, put that instead of A1+B1 in the formula. -- David Biddulph "Lorne" wrote in message ... wow, that is good, but missing what the actual % would be if between 100% and 0%. Sorry if i was not clear on that part. But I am so happy with this so far. Thank you "David Biddulph" wrote: =median(0%,A1+B1,100%) -- David Biddulph "Lorne" wrote in message ... here is my problem, I have three cells a1,b1,c1. a1: fixed, b1: variable, c1: total of both as a %. I need to find the total precent the B1 maybe either (+) or a (-) number. if over 100% to indicate it as "100%", if between 100% and 0% to indicate it as is, and if under 0% to indicate it as a "0%". I am not even sure if its possibe. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting variable length data - outputting fixed character result | Excel Discussion (Misc queries) | |||
Fixed column- Variable row # | Excel Discussion (Misc queries) | |||
Variable column to fixed array | Excel Worksheet Functions | |||
Formatting a number to look like a Percent without a percent sign | Excel Discussion (Misc queries) | |||
reference cell value from fixed column with variable row | Excel Discussion (Misc queries) |