Prev: Range of values
Next: How do I create a travel expense report by employee, by campus
From: Mark D on 1 Apr 2010 08:05
Hi Again

I need a formula that does the following

A1 = 41
B2 = 39
C2 = 6

=IF(B2<A1),C2 BUT DO NOT EXCEED A1

Therefore this should return 2

Any ideas

Thank you
From: Stefi on 1 Apr 2010 08:24
Think over your example!
> Therefore this should return 2 What is this 2, it doesn't show up in the example.

=IF(B2<A1,min(C2,A1),"whatif B2>=A1")

returns C2 if B2<A1 but A1 if C2>A1 (won't exceed A1) but you didn't specify
the case B2>=A1.

--
Regards!
Stefi



„Mark D” ezt írta:

> Hi Again
>
> I need a formula that does the following
>
> A1 = 41
> B2 = 39
> C2 = 6
>
> =IF(B2<A1),C2 BUT DO NOT EXCEED A1
>
> Therefore this should return 2
>
> Any ideas
>
> Thank you
From: "David Biddulph" groups [at] on 1 Apr 2010 08:21
By the sound of it, you didn't mean DO NOT EXCEED A1, but you presumably
mean DO NOT EXCEED A1-B2 ?
You could use =IF(B2<A1,MIN(C2,A1-B2),"whatever you want if B2>=A1")

If the answer you want if B2>=A1 is zero, you might try =MEDIAN(0,B2-A1,C2)
but in that case you might want a further trap to deal with cases where C2
is less than zero.
--
David Biddulph


"Mark D" <MarkD(a)discussions.microsoft.com> wrote in message
news:56219BA6-9E55-4EB9-BC5E-7539FDA3A9C5(a)microsoft.com...
> Hi Again
>
> I need a formula that does the following
>
> A1 = 41
> B2 = 39
> C2 = 6
>
> =IF(B2<A1),C2 BUT DO NOT EXCEED A1
>
> Therefore this should return 2
>
> Any ideas
>
> Thank you


From: Mark D on 1 Apr 2010 09:50
Sorry yep that was silly

If B2 is > than A1 then the result should be 0

"Stefi" wrote:

> Think over your example!
> > Therefore this should return 2 What is this 2, it doesn't show up in the example.
>
> =IF(B2<A1,min(C2,A1),"whatif B2>=A1")
>
> returns C2 if B2<A1 but A1 if C2>A1 (won't exceed A1) but you didn't specify
> the case B2>=A1.
>
> --
> Regards!
> Stefi
>
>
>
> „Mark D” ezt írta:
>
> > Hi Again
> >
> > I need a formula that does the following
> >
> > A1 = 41
> > B2 = 39
> > C2 = 6
> >
> > =IF(B2<A1),C2 BUT DO NOT EXCEED A1
> >
> > Therefore this should return 2
> >
> > Any ideas
> >
> > Thank you
From: Stefi on 1 Apr 2010 10:35
Then
=IF(B2<A1,min(C2,A1),0)

--
Regards!
Stefi



„Mark D” ezt írta:

> Sorry yep that was silly
>
> If B2 is > than A1 then the result should be 0
>
> "Stefi" wrote:
>
> > Think over your example!
> > > Therefore this should return 2 What is this 2, it doesn't show up in the example.
> >
> > =IF(B2<A1,min(C2,A1),"whatif B2>=A1")
> >
> > returns C2 if B2<A1 but A1 if C2>A1 (won't exceed A1) but you didn't specify
> > the case B2>=A1.
> >
> > --
> > Regards!
> > Stefi
> >
> >
> >
> > „Mark D” ezt írta:
> >
> > > Hi Again
> > >
> > > I need a formula that does the following
> > >
> > > A1 = 41
> > > B2 = 39
> > > C2 = 6
> > >
> > > =IF(B2<A1),C2 BUT DO NOT EXCEED A1
> > >
> > > Therefore this should return 2
> > >
> > > Any ideas
> > >
> > > Thank you
 | 
Pages: 1
Prev: Range of values
Next: How do I create a travel expense report by employee, by campus