Prev: HELP!
Next: Sumproduct?
From: Mark D on 24 Mar 2010 11:03
Afternoon everyone.

Have been struggling to get a formula together for the following. I am being
told due to its complexity that I will probably need to break it down into 2
sections but hoping someone can help me simplify things.

I need to do the following

A1 = 1 (although this can be 1,2,3,4,5) (This is my variable I am struggling
with)
B1 = €10000 (TARGET)
C1 - €6500 (ACTUAL)

Then I have a few boxes showing the following

<70% between 70-99% >100%
A5 = 1 B5 = 10% C5 = 15% D5 = 18%
A6 = 2 B6 = 12% C6 = 18% D6 = 21%
A7 = 3 B7 = 14% C7 = 21% D7 = 24%
A8 = 4 B8 = 16% C8 = 24% D8 = 27%
A9 = 5 B9 = 18% C9 = 27% D9 = 30%

So potentially there are 3 statements
1 showing under 70%
1 greater than 70% but less than 99%
1 greater than 100%

=IF(SUM(C1/B1)<70%,C1*B5
=IF(AND(SUM(C1/B1)>70%<90%)),C1*C5 (I know this is wrong)

My problem is I now need to add the variable which is in A1 as it determines
the % applicable in the boxes above.

Apologies if this is confusing but hopefully I have explained it correctly

Thanks in advance for any help.



From: Don Guillett on 24 Mar 2010 11:09
Look again in the help index for AND and you do not need to use sum(unless
summing, of course)
SUM(C1/B1)<70

(C1/B1)<70
or
C1/B1<70
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Mark D" <MarkD(a)discussions.microsoft.com> wrote in message
news:8843E75D-0727-47AB-A964-C1D9E10A23B2(a)microsoft.com...
> Afternoon everyone.
>
> Have been struggling to get a formula together for the following. I am
> being
> told due to its complexity that I will probably need to break it down into
> 2
> sections but hoping someone can help me simplify things.
>
> I need to do the following
>
> A1 = 1 (although this can be 1,2,3,4,5) (This is my variable I am
> struggling
> with)
> B1 = €10000 (TARGET)
> C1 - €6500 (ACTUAL)
>
> Then I have a few boxes showing the following
>
> <70% between 70-99% >100%
> A5 = 1 B5 = 10% C5 = 15% D5 = 18%
> A6 = 2 B6 = 12% C6 = 18% D6 = 21%
> A7 = 3 B7 = 14% C7 = 21% D7 = 24%
> A8 = 4 B8 = 16% C8 = 24% D8 = 27%
> A9 = 5 B9 = 18% C9 = 27% D9 = 30%
>
> So potentially there are 3 statements
> 1 showing under 70%
> 1 greater than 70% but less than 99%
> 1 greater than 100%
>
> =IF(SUM(C1/B1)<70%,C1*B5
> =IF(AND(SUM(C1/B1)>70%<90%)),C1*C5 (I know this is wrong)
>
> My problem is I now need to add the variable which is in A1 as it
> determines
> the % applicable in the boxes above.
>
> Apologies if this is confusing but hopefully I have explained it correctly
>
> Thanks in advance for any help.
>
>
>

From: Ziggy on 25 Mar 2010 16:46
How about something like this?


1 10000 6500

< 70% > 70% < 99% > 100%
0% 70% 10000%
1 10% 15% 18%
2 12% 18% 21%
3 14% 21% 24%
4 16% 24% 27%
5 18% 27% 30%





650 10%

~=HLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1




From: Ziggy on 25 Mar 2010 17:28
On Mar 25, 2:46 pm, Ziggy <ziggy...(a)xmission.com> wrote:
> How about something like this?
>
> 1       10000   6500
>
>         < 70%        > 70% < 99%       > 100%
>         0%      70%     10000%
> 1       10%     15%     18%
> 2       12%     18%     21%
> 3       14%     21%     24%
> 4       16%     24%     27%
> 5       18%     27%     30%
>
>         650             10%
>
>         ~=HLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1

Oops, that 10000% should now be 100%. I played with the formula and
didn't change that.
From: Ziggy on 25 Mar 2010 17:34
On Mar 25, 3:28 pm, Ziggy <ziggy...(a)xmission.com> wrote:
> On Mar 25, 2:46 pm, Ziggy <ziggy...(a)xmission.com> wrote:
>
> > How about something like this?
>
> > 1       10000   6500
>
> >         < 70%        > 70% < 99%       > 100%
> >         0%      70%     10000%
> > 1       10%     15%     18%
> > 2       12%     18%     21%
> > 3       14%     21%     24%
> > 4       16%     24%     27%
> > 5       18%     27%     30%
>
> >         650             10%
>
> >         ~=HLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1
>
> Oops, that 10000% should now be 100%. I played with the formula and
> didn't change that.

This leaves the A1 as a manual input.

If you're asking what I think you're asking, you're asking about a
circular reference. You want the formula to pick A1 but the formula
is dependent on the value in A1.

Or? What determines A1?
 | 
Pages: 1
Prev: HELP!
Next: Sumproduct?