correction factor

[6foot5]

Hello,

I am a more or less a beginner with respect to Excel and need a little help here.
I am a poker player and keep track of my winnings and losses in an Excel Sheet
with diagrams etc.

In essence the important part of the sheet looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]location
[/TD]
[TD]net
[/TD]
[TD]total
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York
[/TD]
[TD]1200
[/TD]
[TD]1200
[/TD]
[/TR]
[TR]
[TD]Berlin
[/TD]
[TD]-400
[/TD]
[TD]800
[/TD]
[/TR]
[TR]
[TD]Peking
[/TD]
[TD]200
[/TD]
[TD]1000
[/TD]
[/TR]
</tbody>[/TABLE]


The formula so far for 'total' is =SUM($B$3:$B$3) which I
reuse by pressing 'Ctrl-D' in the next field in that column etc.
Easy enough.


Now I want to take currency exchange differences into account and need the 'total' cell
to multiply by a certain factor or, depending on if the location was in the Euro-zone, not.

Let's say I want

a.) New York's total result to be multiplied by 0.8
a.) Berlin's result not to be multiplied at all and
c.) Peking's result to be multiplied by 7.5

How can extend or replace the above formula to accomplish this?

As I said, I am kind of a beginner in Excel and there is probably a much better solution
out there in space. I just write into the first cell the location I played at, in the 'net'
column the result of the night and hit 'Ctrl-D' in the 'total' cell...

 

[JoeStellar]

So many ways to do this. One way is a simple if formula, made not so simple by embedding IF formulas within IF formulas, but it is straight forward. =IF(A1="New York",1,IF(A1="Berlin",0.8,IF(A1="Peking",0.5,1))) If Cell A1 = New York then multiplier is 1 if it's not New York then Use another IF statement to determine if it's not Berlin either then then just use 1. You could also use lookup tables or cell offsets based on formulas. Type =IF into the formula bar to get the prompts for filling in the formula variables. Logical test, value if true, value if false.

 

[Eric W]

Welcome to the Board!

Try:

	A	B	C	D	E	F	G
New York
Berlin
Peking

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]net[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]total[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]multiplier[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]960[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Berlin[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-400[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]560[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Peking[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2060[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2060[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUMPRODUCT($B$2:$B2,SUMIF($E$2:$E$4,$A$2:$A2,$F$2:$F$4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Put the formula in C2 and drag down. Make a table of your locations and multipliers in E:F, change all the references to match.

 

[6foot5]

So many ways to do this. One way is a simple if formula, made not so simple by embedding IF formulas within IF formulas, but it is straight forward. =IF(A1="New York",1,IF(A1="Berlin",0.8,IF(A1="Peking",0.5,1))) If Cell A1 = New York then multiplier is 1 if it's not New York then Use another IF statement to determine if it's not Berlin either then then just use 1. You could also use lookup tables or cell offsets based on formulas. Type =IF into the formula bar to get the prompts for filling in the formula variables. Logical test, value if true, value if false.

I'm past that problem and quickly found a solution myself.
Mine looks like similar to this:

=IF(OR(A25="NewYork";A25="LasVegas";A25="AtlanticCity");E25*1.15;IF(OR(A25="Manila");E25/50;E25))

Thanks