Prev: Negative result gives #### is there a way to show the actual - num
Next: updating data in worksheet..!
From: Clubber2010 on 6 Mar 2010 08:24 Hi, i am trying to do something on excel, but cannot for the life of me figure what formula i should write.. This may help if i explain first - I have a column with a number in it (the answer)... I have a column with the fraction 1/20 in it.. Then i want a column that will multiply these 2 columns together If the answer is 0, it goes down as <20 If the answer is 1, it goes down as 20 If the answer is 2, it goes down as 40 etc (like it is set out below)... (0) * (1/20) = (<20) (1) * (1/20) = (20) (2) * (1/20) = (40) This is probably so simple, but I cannot think at all.. In the last column all i keep getting is #NA rather than what i have above!
From: Fred Smith on 6 Mar 2010 11:10
It looks to me that what you are doing is *dividing* by the fraction. If a1 is your answer, and b1 is the fraction, try: =a1/b1 If that's correct, the only issue is what you want when the answer is zero, so try: =if(a1=0,"<20",a1/b1) Regards, Fred "Clubber2010" <Clubber2010(a)discussions.microsoft.com> wrote in message news:C15D8DF3-47E6-42EC-96AB-4A8BA8FC610D(a)microsoft.com... > Hi, i am trying to do something on excel, but cannot for the life of me > figure what formula i should write.. > > This may help if i explain first - > > I have a column with a number in it (the answer)... > I have a column with the fraction 1/20 in it.. > Then i want a column that will multiply these 2 columns together > > If the answer is 0, it goes down as <20 > If the answer is 1, it goes down as 20 > If the answer is 2, it goes down as 40 etc > > (like it is set out below)... > > > (0) * (1/20) = (<20) > (1) * (1/20) = (20) > (2) * (1/20) = (40) > > This is probably so simple, but I cannot think at all.. > In the last column all i keep getting is #NA rather than what i have > above! |