IFS, rounding and absolute values

[N0t Y0urs]

I am not sure if I am over complicating this or not.

I have the following set up and while excel is great and accurate I need to figure out how to achieve the result I want.

So I have this formula in my size column - =IFS(B13="Aj",(((G12*C13))/$C$3)*$H$4,B13="an",(((G12*B13))/$C$3)*$H$5,B13="au",(((G12*C13))/$C$3)*$F$6,B13="achf",(((G12*C13))/$C$3)*$F$5,B13="acad",(((G12*C13))/$C$3)*$F$4,B13="chfj",(((G12*C13))/$C$3)*$H$4,B13="cadj",(((G12*C13))/$C$3)*$H$4,B13="ea",(((G12*C13))/$C$3)*$H$3,B13="echf",(((G12*C13))/$C$3)*$F$5,B13="ecad",(((G12*C13))/$C$3)*$F$4,B13="eg",(((G12*C13))/$C$3)*$H$3,B13="eu",(((G12*C13))/$C$3)*$F$6,B13="en",(((G12*C13))/$C$3)*$H$5,B13="ga",(((G12*C13))/$C$3)*$H$3,B13="gcad",(((G12*C13))/$C$3)*$F$4,B13="gchf",(((G12*C13))/$C$3)*$F$5,B13="gj",(((G12*C13))/$C$3)*$H$4,B13="gn",(((G12*C13))/$C$3)*$H$5,B13="gu",(((G12*C13))/$C$3)*$F$6,B13="nj",(((G12*C13))/$C$3)*$H$4,B13="nchf",(((G12*C13))/$C$3)*$F$5,B13="ncad",(((G12*C13))/$C$3)*$F$4,B13="nu",(((G12*C13))/$C$3)*$F$6,B13="uj",(((G12*C13))/$C$3)*$H$4,B13="uchf",(((G12*C13))/$C$3)*$F$5,B13="ucad",(((G12*C13))/$C$3)*$F$4,B13="xau",(((G12*C13))/$C$3)*$H$6,B13="xag",(((G12*C13))/$C$3)*$J$3,B13="aus",(((G12*C13))/$C$3)*$F$4,B13="nas",(((G12*C13))/$C$3)*$J$5,B13="nikkei",(((G12*C13))/$C$3)*$J$6,B13="ftse",(((G12*C13))/$C$3)*$L$3,B13="us2000",(((G12*C13))/$C$3)*$L$4,B13="dow",(((G12*C13))/$C$3)*$L$5,B13="s&p",(((G12*C13))/$C$3)*$L$6,B13="dxy",(((G12*C13))/$C$3)*$N$3) and it works but just shudder when I look at it so if it could be more efficient let me know please.

From there I use it to calculate my results. But this is where I get stuck. If I take row 13 for example my size reports as 0.06 I would like to make sure that it an absolute value because this impacts me further into my other calculations. In my results I need to create a formula that does the following:

It looks at B13 (Asset) so it knows what $ value to use. The simple instruction is this (in absolute numbers versus what excel reports)

((((D13*C8)*C4*10*H6)+((D13*C8)*C5*10*H6)+((D13*C6*10*H6)-((D13*C8)*C4*10*H6)+((D13*C8)*C5*10*H6)) = his gives the result of $24.97 however the result should actually be $20.93

So to explain what I am trying to do is use excel to forecast. I want the formula to do something like this:

Results = 10% of size x 12.5 (TP1) x 10 x XAU (Asset identifier B13 = Asset value of H6) + 10% of size x 18.5 (TP1) x 10 x XAU (Asset identifier B13 = Asset value of H6) + 80% of size x 32.5 (TP1) x 10 x XAU (Asset identifier B13 = Asset value of H6) + 0% of size x 225 (TP1) x 10 x XAU (Asset identifier B13 = Asset value of H6)

So based on a size of 0.06 it would be:
0.01 x 12.5 x 10 x 1.30 + 0.01 x 18.5 x 10 x 1.30 + 0.04 x 32.5 x 10 x 1.30 + 0 x 225 x 10 x 1.30

Hope that makes sense as I have spent the weekend reading experimenting with IFERRORS, IFS, ROUNDS and ABSOLUTEs and still stuck

Thanks in advance

 

[StephenCrump]

Step 1: You could simplify your first formula using a double lookup:

	A	B	C	D	E	F
1
2		Table1			Mapping
3		AUD	1.00		AJ	JPY
4		CAD	1.15		AN	NZD
5		CHF	1.10		DXY	DXY
6		etc			EA	GBP
7		GBP	1.25		NCAD	CAD
8		etc			NIKKEI	NIKKEI
9		DXY	1.27		UCAD	CAD
10					etc
11		Find	Result
12		NCAD	1.15
13		EA	1.25
14		DXY	1.27
15
Sheet6

Cell Formulas
Range	Formula
C12:C14	C12	=VLOOKUP(VLOOKUP(B12,Mapping,2,),Table1,2,)

Named Ranges
Name	Refers To	Cells
Mapping	=Sheet6!$E$3:$F$9	C12:C14
Table1	=Sheet6!$B$3:$C$9	C12:C14

I haven't tried to work out yet what you're doing after that.

 

[N0t Y0urs]

Thanks for the suggestion and I think this might reduce the formula however, I must be doing something completely wrong.

Here is the temporary sheet I am working with Workings

So using the double VLOOKUP it is referencing something completely different to what I want. I cannot see the logic in where I went wrong. I am asking C15 to look at the Mapping data set which will return XAU which in turns looks at Table 1 and should return 8 but it is returning 18. I have tried different Asset values like AJ which would reference JPY then 4 not 6 so if you could explain where I went wrong that would be amazing because I see the logic but for the life of me can't get the right answer.

Also is this the way to go if I will ultimately have 101 different data sets with 500 rows of data?

 

[StephenCrump]

So close! In Sample!E15, you have:

=VLOOKUP(VLOOKUP(C15,Mapping,2),Table1,2,)

But you need an exact match:

=VLOOKUP(VLOOKUP(C15,Mapping,2,),Table1,2,)

 

[N0t Y0urs]

Ah, thanks cause I ended up adding another sheet and it worked but still didn’t see the silly error. Will see if this helps me and will reach out on the next hurdle

 

[N0t Y0urs]

Ok so now I am back with my original question. So the link is still active on my existing spreadsheet.

I’ve managed to get rid of all the ifs in my above formula and replaced it with the following:

=(D2*$D14)/$D$3* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,)

So that gives me a size, and in this case let’s assume it’s 0.1. From here I need to calculate the following:

=(E14*$D$8)*$D$4*10* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,)+ (E14*$D$9)*$D$5*10* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,) + (E14*$D$10)*$D$6*10* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,) + (E14*$D$11)*$D$7*10* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,)

That works as well because it reflects 10% as 0.01 but I need to have the formula reflect an absolute or rounded version if it’s not as neat as .10 for example

0.05 would be 10%=0.01 not 0.005. The reason for needing the rounded value is forecasting requirements as it impacts where increases can be made.

 

[StephenCrump]

In the workbook you've posted, your first four "sizes" (Sample!E14:E17) are 0.0202, 0.0187, 0.0315 and 0.0271.

I think you are saying you want these values rounded up, before applying the longer formula in Post #6?

How will this rounding work, e.g. should these values be rounded up to 0.03, 0.02, 0.04 and 0.03? What about larger sizes - these go to ~1 million - how do you want to round these?

By the way, you can simplify:

=(E14*$D$8)*$D$4*10* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,)+ (E14*$D$9)*$D$5*10* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,) + (E14*$D$10)*$D$6*10* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,) + (E14*$D$11)*$D$7*10* VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,)

to

=SUM(E14*$D$8:$D$11*$D$4:$D$7*10)*VLOOKUP(VLOOKUP(C14,Mapping,2,),Table1,2,)
(In versions earlier than 365, array-enter or use SUMPRODUCT instead of SUM)

 

[N0t Y0urs]

Yes that what I am wanting and I also have a max of 250 so that eliminates the exponential growth. The max variable I mention as I needed to figure this section first

 

[StephenCrump]

So, like this?

	A	B
1	0.0202	0.0300
2	0.0187	0.0200
3	0.0315	0.0400
4	0.0271	0.0300
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=ROUNDUP(A1,2)

 

[N0t Y0urs]

Yep with a max value of 250. Am I asking to much?