FORMULA

[ddragas]

Hi
How can I make more than 7 checking (IF) in formula?
THNX

 

[DonkeyOte]

the only way around it is to combine cells...so run an IF over more than once cell...

so you last (if true) in the first cell (eg A1) would be B1

in B1 you then continue the if - so the result will eventually be returned in B1 and therefore picked up in A1.

Would suggest you post what you're trying to do here - I'd put money on it that someone can create a different formula that will do the trick!

 

[Aladin Akyurek]

A lookup formula should accomplish what you're asking. Repost (stay in this thread) with the exact layout of the data in your workbook (worksheet names, columns, rows, etc. involved) along with a small sample and expected results.

 

[ddragas]

It is a litle bit complicated, but I'll try to explain

It's all about discount (%)

I've got 15 conditions.
Cell D1 is costumer
Range("B15:B39) are diferent Codes of products

Now let's start
If(Vlookup(B15;Šifre!B2:G65535;6)="P";If(Vlookup(B15;Kupci!B2:M65535;12)="I";KOEFICIJENTI!D12))))

there are letters that specify tipe of article:
P
D
Š4
R3
K

and tipe of costumer:

I
R
E

All together 15 conditions.

Every product has a diferent discount depending of costumer.

Until now 25000 products, and 700 costumers.



Or to resolve problem with a macro?
Something based on folowing:


Dim fn As WorksheetFunction, LookupVal As Variant
Set fn = WorksheetFunction
On Error Resume Next
If fn.VLookup(Sheets("Radno").Range("B15"), Sheets("Šifre").Range("b:g"), 6, False)="P" And
If fn.VLookup(Sheets("Radno").Range("D1"), Sheets("Kupci").Range("b:m"), 12, False) = "I" Then
Sheets("Radno").Range("F15") = Sheets("KOEFICIENTI").Range("D12")


Hope U've understood me.
THNX

 

[Aladin Akyurek]

On 2002-11-07 08:09, ddragas wrote:
It is a litle bit complicated, but I'll try to explain

It's all about discount (%)
[..]

Not sure what follows can be of any help to you... I cooked up the example by guessing what you need...

Book5
	A	B	C	D	E	F
1		CustomerType	ArticleType	Discount
2	t1-@-a1	t1	a1	2%
3	t1-@-a2	t1	a2	2.30%
4	t1-@-a3	t1	a3	3%
5	t2-@-a3	t2	a3	3.30%
6	t1-@-a4	t1	a4	4%
7	t3-@-a4	t3	a4	4.50%
8
9	LightgreenareaisnamedasTable.
10
11	Customer	Quantity	CustomerType	ArticleType	Discount?
12	Cust1	40	t1	a3	0.03
13
Sheet1

Hope this helps.

 

[Dejan]

It should go like this:<table x:str border=0 cellpadding=0 cellspacing=0 width=929 style='border-collapse:
collapse;table-layout:fixed;width:697pt'><col width=64 span=2 style='width:48pt'><col class=xl24 width=84 style='mso-width-source:userset;mso-width-alt:3072;
width:63pt'><col width=64 span=3 style='width:48pt'><col width=77 style='mso-width-source:userset;mso-width-alt:2816;width:58pt'><col class=xl24 width=64 style='width:48pt'><col width=64 span=6 style='width:48pt'><tr height=17 style='height:12.75pt'><td height=17 class=xl31 colspan=2 width=128 style='height:12.75pt;
mso-ignore:colspan;width:96pt'>Customers</td><td class=xl27 width=84 style='width:63pt'> </td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td class=xl32 width=64 style='width:48pt'>Products:</td><td class=xl26 width=77 style='width:58pt'> </td><td class=xl26 width=64 style='width:48pt'> </td><td width=64 style='width:48pt'></td><td class=xl35 width=64 style='width:48pt'> </td><td class=xl38 width=64 style='border-left:none;width:48pt'>I</td><td class=xl38 width=64 style='border-left:none;width:48pt'>R</td><td class=xl38 width=64 style='border-left:none;width:48pt'>E</td><td class=xl31 width=64 style='width:48pt'>Customers</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>1</td><td class=xl27>Mike</td><td class=xl28>I</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1000</td><td class=xl25>AirCondition</td><td class=xl26>P</td><td></td><td class=xl37 style='border-top:none'>P</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.1">10%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.15">15%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.2">20%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>2</td><td class=xl27>John</td><td class=xl28>R</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1100</td><td class=xl25>Exaust</td><td class=xl26>D</td><td></td><td class=xl37 style='border-top:none'>D</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.08">8%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.05">5%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.15">15%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>3</td><td class=xl27>Peter</td><td class=xl28>E</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1200</td><td class=xl25>Burner</td><td class=xl26>R3</td><td></td><td class=xl37 style='border-top:none'>R3</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.15">15%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.1">10%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0">0%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>4</td><td class=xl27>Mark</td><td class=xl28>E</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1300</td><td class=xl25>Boiler</td><td class=xl26>Š4</td><td></td><td class=xl37 style='border-top:none'>Š4</td><td class=xl36 style='border-top:none;border-left:none' x:num="0">0%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.05">5%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.15">15%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>5</td><td class=xl27>Jorge</td><td class=xl28>I</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>1400</td><td class=xl25>ScrewDriver</td><td class=xl26>K</td><td></td><td class=xl37 style='border-top:none'>K</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.2">20%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.05">5%</td><td class=xl36 style='border-top:none;border-left:none' x:num="0.1">10%</td><td></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>6</td><td class=xl27>Michael</td><td class=xl28>R</td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>6</td><td class=xl25>etc....</td><td class=xl26>D</td><td></td><td class=xl32>Products:</td><td colspan=4 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>7</td><td class=xl27>etc......</td><td class=xl28> </td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>7</td><td class=xl25>etc....</td><td class=xl26>R3</td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl27 align=right style='height:12.75pt' x:num>8</td><td class=xl27>etc......</td><td class=xl28> </td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>8</td><td class=xl25>etc....</td><td class=xl26>Š4</td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td><td class=xl24></td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>9</td><td class=xl25>etc....</td><td class=xl26> </td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td><td class=xl24></td><td colspan=2 style='mso-ignore:colspan'></td><td class=xl25 align=right x:num>10</td><td class=xl25>etc....</td><td class=xl26> </td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td><td class=xl24></td><td colspan=4 style='mso-ignore:colspan'></td><td class=xl24></td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td><td class=xl24></td><td colspan=4 style='mso-ignore:colspan'></td><td class=xl24></td><td colspan=6 style='mso-ignore:colspan'></td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl33 colspan=2 style='mso-ignore:colspan'>WORKING AREA:</td><td class=xl34 align=right x:num>4</td><td class=xl34
>Mark</td><td colspan=9 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl29> </td><td class=xl30> </td><td colspan=11 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl29> </td><td class=xl30> </td><td colspan=11 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30>Code</td><td class=xl30>Article</td><td class=xl30>Quantity</td><td class=xl30>Discount</td><td colspan=9 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1000</td><td class=xl30
AirCondition</td><td class=xl30 x:num>1</td><td class=xl29 colspan=2 style='mso-ignore:colspan'>Here goes formula:</td><td colspan=8 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1100</td><td class=xl30
Exaust</td><td class=xl30 x:num>5</td><td class=xl29 colspan=10 style='mso-ignore:colspan'>IF(VLOOKUP(B19;$F$2:$H$11;3)="D";AND(IF(VLOOKUP($D$14;$A$2:$C$9;3)="I";$K$3)
etc.............)))</td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1200</td><td class=xl30
Burner</td><td class=xl30 x:num>8</td><td colspan=10 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1300</td><td class=xl30
Boiler</td><td class=xl30 x:num>3</td><td colspan=10 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl30 x:num>1400</td><td class=xl30
ScrewDriver</td><td class=xl30 x:num>4</td><td colspan=10 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl29> </td><td class=xl30> </td><td colspan=11 class=xl29 style='mso-ignore:colspan'> </td></tr><tr height=17 style='height:12.75pt'><td height=17 style='height:12.75pt'></td><td class=xl29> </td><td class=xl30> </td><td colspan=11 class=xl29 style='mso-ignore:colspan'> </td></tr><![if supportMisalignedColumns]><tr height=0 style='display:none'><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=84 style='width:63pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=77 style='width:58pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td><td width=64 style='width:48pt'></td></tr><![endif]></table></body></html>


Sory For taken space. HtmlMaker2.32 doesnt work on my machine.
This message was edited by Dejan on 2002-11-07 12:00

 

[Dejan]

Can somebody help me???