Simplifying Excel Formula

[Yakawa]

Hello, I'm trying to streamline a work process with an excel spreadsheet. I want to be able to make a price calculation via a spreadsheet. The problem is – I have too many columns to pull data from. I’m looking if there is an easier and cleaner way to make the document work. At the moment the working formula is ~830 characters long, and I’m missing around 100-150 more. Link to Google Docs
For every drop-down there will be a value on a separate sheet to pull from. And with the IF function it gets a bit looong

While for a one time deal this can be bearable, it will be a pain to fix something in the future. I putting a link to google docs sheet with an example of what I’m looking for. Any and all suggestions and recommendations will be appreciated!

 

[Gerald Higgins]

Hi. welcome to the board.

I don't want to follow your link, but otherwise willing to have a look at it.

Please can you post your current formula, and say exactly how you want to change it ?
To simplify things, if you have complicated sheet names, please replace these with "Sheet1", "Sheet2" etc, for the purposes of this post.

 

[Yakawa]

Hey, thank you for the replay!
So the formula is

 =IF(D3="None",(Prices!C2C3),IF(D3="white 2",(Prices!C3C3),IF(D3="white 3",(Prices!C4C3),IF(D3="white 4",(Prices!C5C3),IF(D3="white 5",(Prices!C6C3),IF(D3="white 6",(Prices!C7C3),IF(D3="white 8",(Prices!C8C3),IF(D3="white 10",(Prices!C9C3),IF(D3="Clear 3",(Prices!C10C3),IF(D3="Clear 4",(Prices!C11C3),IF(D3="Clear 5",(Prices!C12C3),IF(D3="Clear 6",(Prices!C13C3),IF(D3="Clear 8,(Prices!B14C2),IF(D2=""Clear10мм",(Prices!C15C3))))))))))))))+IF(E3="Yes",(((A32+B32)Prices!C17)/1000),IF(E3="No",0))+IF(F3="Yes",(C3Prices!C18),IF(F3="No",0))+IF(H3="none",0,IF(H3="One",Prices!E1,IF(H3="two",(Prices!E12),IF(H3="three",(Prices!E13),IF(H3="four",(Prices!E14),IF(H3="five",(Prices!E15),IF(H3="six",(Prices!E16),IF(H3="seven",(Prices!E17),IF(H3="four",(Prices!E18),IF(H3="nine",(Prices!E19),IF(H3="ten",(Prices!E1*10))))))))))))

Every IF is a drop-down menu, and in the formula there are 2 columns missing (2 more drop-down menus). I'm looking if there is a way to simplify the whole formula so that it can be easier to edit it in the future.

PS: I'm not 100% if I'm clear with what I need and want but I can provide whatever info is needed.

 

[Gerald Higgins]

Hi there.

I'm not familiar with this style of notation . . .

=IF(D3="None",(Prices!C2C3)

and specifically the C2C3 element.
Is that a range name ?
Or is it some kind of cell reference ? Are you perhaps using Excel in a version where English is not the language ?

If it's a cell reference of some kind, then you can probably simplify your formula A LOT, by replacing many of the IF statements with a single lookup formula and a lookup table.

If you can explain the C2C3 references a bit more, I can provide more details on how to build the lookup formula.

 

[Yakawa]

Hey, this should be an asterix (for * multiplication). No idea why it disappeared, at the bottom I will paste the code again. The C3 on main sheet is the m2 value and the Prices!C3-C15 are different material prices. Also I am using excel 2013 in English, but I'm able to make the sheet in office 365 if there will be compatibility with Google docs/OpenOffice.

=IF(D3="None",(Prices!C2*C3),IF(D3="white 2",(Prices!C3*C3),IF(D3="white 3",(Prices!C4*C3),IF(D3="white 4",(Prices!C5*C3),IF(D3="white 5",(Prices!C6*C3),IF(D3="white 6",(Prices!C7*C3),IF(D3="white 8",(Prices!C8*C3),IF(D3="white 10",(Prices!C9*C3),IF(D3="Clear 3",(Prices!C10*C3),IF(D3="Clear 4",(Prices!C11*C3),IF(D3="Clear 5",(Prices!C12*C3),IF(D3="Clear 6",(Prices!C13*C3),IF(D3="Clear 8,(Prices!B14*C2),IF(D2=""Clear10мм",(Prices!C15*C3))))))))))))))+IF(E3="Yes",(((A3*2+B3*2)*Prices!C17)/1000),IF(E3="No",0))+IF(F3="Yes",(C3*Prices!C18),IF(F3="No",0))+IF(H3="none",0,IF(H3="One",Prices!E1,IF(H3="two",(Prices!E1*2),IF(H3="three",(Prices!E1*3),IF(H3="four",(Prices!E1*4),IF(H3="five",(Prices!E1*5),IF(H3="six",(Prices!E1*6),IF(H3="seven",(Prices!E1*7),IF(H3="four",(Prices!E1*8),IF(H3="nine",(Prices!E1*9),IF(H3="ten",(Prices!E1*10))))))))))))

 

[Gerald Higgins]

If it's a missing ASTERISK, that's fine, I understand.
If it's a missing ASTERIX, I can't help you with that (google ASTERIX if you need more help on that )

OK, so assuming it's a missing ASTERISK, then let's have a go.

First of all, you can simplify much of this by using a lookup table to deal with the tests of D3.
This won't solve your entire issue, but it will make it much easier to handle.
We can probably deal with some other bits later on.

Set up a table somewhere like this . . .
None...........=Prices!C2
white 2........=Prices!C3
white 3........=Prices!C4

and so on.

Let's say you set up a new sheet called LOOKUPSHEET, and you set up the above table in columns A and B of that new sheet.
In column A, write in all your test values, like "None", "White 2" and so on.
In column B, insert formulas as I've shown that pull in the values from the relevant cell in the Prices tab.

Then, use a formula like this
=vlookup(D3,LOOKUPSHEET!A1:B100,2,false)*C3

Replace "B100" with whatever the last entry is in column B in your lookup table.

This should deal with all the tests of D3.

If you can get this bit working, we can then move on to the other parts.

 

[Yakawa]

Hey, well i couldn't find the "edit" button on the forum

I looked into VLOOKUP, but I'm not sure it will help me. From what i understand it's to pull from a table and show me an already known value. (or I haven't understood the few articles and videos i checked.) But if you are certain Lookup is the way for me i will be going over some articles about it again.
What i need is:
Sheet 1 (main)
[TABLE="width: 500"]
<tbody>[TR]
[TD]width[/TD]
[TD]height[/TD]
[TD]m2[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]y[/TD]
[TD]x*y[/TD]
[TD]Drop-down[/TD]
[TD]drop-down[/TD]
[TD]drop-down[/TD]
[TD]drop-down[/TD]
[TD]drop-down[/TD]
[TD]Result
[/TD]
[/TR]
</tbody>[/TABLE]

D - the material eg. None
White 1
White 2
E - finish eg. Gloss
Matte
F - edges eg. Sharp
Soft
G - hole quantity eg. None
1
H - Hole size eg. 10mm
15mm
I - the result from all the calculations.

Sheet 2 (prices)
For every column there is a table with a set price.


Now the formula i'm using is working, but if me or someone else wants to edit something in the future its too long and easy to break in my opinion.

 

[Gerald Higgins]

I would suggest looking into the LOOKUP and VLOOKUP functions would be very useful for you, if you're not familiar with them.
I'm very confident that they can fix part of your problem - probably not all of it but they can hugely simplify your task.
One reason is the point you make in your final sentence - that if something needs to change in future, then editing the lookup table will probably be much easier than editing the formula.