Conditional Formula in Excel

[Guest]

i want to design a conditional formaula in excel
------------------------------------------------------------------------
A B C D E
------------------------------------------------------------------------
1.1 7.7 0.5 10 A 1.
2.2 8.8 0.4 20 C 0.
3.3 9.9 0.3 30 A 3.
4.4 14 0.2 40 B 1
5.5 15 0.1 50 S
6.6 16 33.47 60 C 33.4
------------------------------------------------------------------------
it should return F = cross ref of D1:F1 = 1.

i.e it should return cross section of 10 : A and it should return 1.1 in F colum
Is it possible using vlookup functio

thanx and regards
Ame

 

[Frank Kabel]

Hi
not sure how you come toi this result. How do you come to this result

 

[Peo Sjoblom]

I can't see any logic in this?

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Domenic]

Hi,

If you're using only column D and E for your criteria, try:

=INDEX(F1:F6,MATCH(1,(D16=B9)*(E1:E6=B10),0)) to be entered using
CTRL+SHIFT+ENTER

This assumes that B9 houses your criteria for column D (10) and B10
houses your criteria for column E (A).

If you have more criteria, you can adjust the formula to take those into
consideration.

Hope this helps!

 

[Frank Kabel]

Hi
no I don't get it. What do you mean with cross ref of 10 and col A. Is
this an algorithmn or is this a lookup table. I don't know how you get
from the value '10' and col A to a result of 1.1 (do you divide
something?)

 

[Frank Kabel]

Hi Peo
happy I'm not alone. Thought I just didn't get it due to lack of coffee

 

[Peo Sjoblom]

Or if the values in F are numeric just use sumproduct

=SUMPRODUCT(((D16=B9)*(E1:E6=B10)*(F1:F6)))

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Domenic]

Amey,

Now, I'm with both Frank and Peo. I have no idea how you get your
results.

 

[Harlan Grove]

i want to design a conditional formaula in excel.
-------------------------------------------------
A B C D E F
-------------------------------------------------
1.1 7.7 0.5 10 A 1.1
2.2 8.8 0.4 20 C 0.4
3.3 9.9 0.3 30 A 3.3
4.4 14 0.2 40 B 14
5.5 15 0.1 50 S 0
6.6 16 33.47 60 C 33.47

...

If the table above were in A1:F6, your column F could be generated with the
following formula in cell F1

=INDIRECT(E1&INT(D1/10))

filled down into F2:F6. It's unclear whether you really do want column D values
to correspond to rows equal to INT(D#/10), but it does appear so from your
example. If this isn't what you meant/want, provide more details, but try to
make fewer typos (e.g., "F = cross ref of D1:F1" should be "F = cross ref of
D1:E1").

 

[Guest]

dear Harlan Grove,

it worked!!! Thanx a lot.
For the rest of the contributors, what I meant by cross ref was......
if Value in cell d1=10 and value in adjecent cell E1="A" then value in F1 should be
value corresponding to intersection of D1 and A1 i.e. 1.1
i.e col D will remain constant but depending upon value of col E,
the lookup col will change A or B or C.

I hope its clear now.
Anyways lots of thanx to Harlan

regards,
Amey Naware