Mass Formula with IF & LOOKUP

[Finalmakerr]

Hello,

Formula: =IF(LOOKUP("$",G10,F10),F10*$K$3,IF(LOOKUP("€",G10,F10),F10*$K$4,IF(LOOKUP("₪",G10,F10),F10,"")))

Issue : Only the first "IF" is working properly while the rest result in N/A#

Formula goal: Currency check and exchange


Further details:

CELL G ($,€,₪) = CURRENCY TYPE
CELL F = PRICE
CELL K = CURRENCY RATE


Thanks in advance,

Gal

 

[Finalmakerr]

I did it!

=IF($J50="₪",(IF($J$22="$",F22*$K$3)+(IF($J$22="€",F22*$K$4))+(IF($J$22="₪",F22)))+(IF($J$29="$",F29*$K$3)+(IF($J$29="€",F29*$K$4))+(IF($J$29="₪",F29)))+(IF($J$41="$",F41*$K$3)+(IF($J$41="€",F41*$K$4))+(IF($J$41="₪",F41))))+IF($J50="$",(IF($J$22="$",F22)+(IF($J$22="€",F22*($K$4-$K$3)))+(IF($J$22="₪",F22/$K$3)))+(IF($J$29="$",F29)+(IF($J$29="€",F29*($K$4-$K$3)))+(IF($J$29="₪",F29/$K$3))+(IF($J$41="$",F41))+(IF($J$41="€",F41*($K$4-$K$3)))+(IF($J$41="₪",F41/$K$3))))+IF($J50="€",(IF($J$22="$",F22/($K$4-$K$3))+(IF($J$22="€",F22))+(IF($J$22="₪",F22/$K$4)))+(IF($J$29="$",F29/($K$4-$K$3))+(IF($J$29="€",F29))+(IF($J$29="₪",F29/$K$4))+(IF($J$41="$",F41/($K$4-$K$3))+(IF($J$41="€",F41))+(IF($J$41="₪",F41/$K$4)))))

 

[Marcol]

You really must consider adding flags to your worksheet.
Without using some more easily identifiable flags your task is going to be really messy and difficult to maintain.

This is a bit simpler than your solution, but needs the workbook amended as in the link below

=((SUMIF($J$8:$J$50,"$",I8:I49)*$K$3)+(SUMIF($J$8:$J$49,"€",I8:I49)*$K$4))/IF($K$51="€",$K$4,IF($K$51="$",$K$3,1))*$K$5

Have a look at this file on my skydrive FinalmakerrBook2

When you open this file you'll get this message

The workbook cannot be opened because it contains the following features that cannot be displayed in the browser:

• Data validation

Click "Open in Excel"

You may have to sign in to Sky Drive to see this file, it's a free service.

Hope this helps.

 

[Finalmakerr]

Marcol thank you for all your help everytime

I saw your file and i see that you manage to use a similar table to my original and overdue my problem with the design of the subtotal. From your post i assume it's related to "FLAG" thing but i don't know how you did that (XX:YY is not including the subtotals between them).

 

[Marcol]

Flag is just a term for an identifier in this case it is the currency symbol in columns J & K.
The subtotal rows,count locally(blue) to convert the value to NIS, then uses the flag in Column K (red) to convert to the currency you require
e.g.
In F29

=(SUMIF([COLOR=blue]$J$23:$J$28,"₪",[/COLOR]F23:F28)+(SUMIF([COLOR=blue]$J$23:$J$28[/COLOR][COLOR=blue],"$",[/COLOR]F23:F28)*$K$3)+(SUMIF([COLOR=blue]$J$23:$J$28,"€",[/COLOR]F23:F28)*$K$4))/IF([COLOR=red]$K$29[/COLOR][COLOR=red]="€",[/COLOR][COLOR=black]$K$4[/COLOR],IF([COLOR=red]$K$29[/COLOR][COLOR=red]="$",[/COLOR]$K$3,1))

The Totals work in a similar way but uses the whole table
e.g.
In F50

=(SUMIF([COLOR=blue]$J$8:$J$49,"₪",[/COLOR]F8:F49)+(SUMIF([COLOR=blue]$J$8:$J$49,"$",[/COLOR]F8:F49)*$K$3)+(SUMIF([COLOR=blue]$J$8:$J$49,"€",[/COLOR]F8:F49)*$K$4))/IF([COLOR=red]$K$50="€",[/COLOR]$K$4,IF([COLOR=red]$K$50="$",[/COLOR]$K$3,1))

Because the words "Sub Totals" in Column J is not a currency flag then these rows are not included when totalling up the full table.

The commission again works similarly to Totals but omits NIS and Sub Totals and is finally multiplied by the commission rate (green).
e.g.
In F51

=((SUMIF([COLOR=blue]$J$8:$J$50,"$",[/COLOR]F8:F49)*$K$3)+(SUMIF([COLOR=blue]$J$8:$J$49,"€",[/COLOR]F8:F49)*$K$4))/IF([COLOR=red]$K$51="€",[/COLOR]$K$4,IF([COLOR=red]$K$51="$",[/COLOR]$K$3,1))*[COLOR=green]$K$5[/COLOR]

Where does "XX:YY" come into the picture?