Need serious help with correct function and size control Please Please Help

[alxanderjon]

I'm pretty new to excel, so I don't know exactly what's going on here or why, but I have this massive formula:


=IF(AND(L30<=1740,N30=1,O30="yes"),C30/1000*2.18,IF(AND(L30<=1740,N30=1),C30/1000*1.97,IF(AND(L30<=1740,N30=2),C30/1000*1.84,IF(AND(L30<=1740,N30=2,O30="yes"),C30/1000*2.05,IF(AND(L30>1740,L30<=1828,N30=1,O30="yes"),C30/1000*2.17,IF(AND(L30>1740,L30<=1828,N30=1),C30/1000*1.96,IF(AND(L30>1740,L30<=1828,N30=2),C30/1000*1.83,IF(AND(L30>1740,L30<=1828,N30=2,O30="yes"),C30/1000*2.04,IF(AND(L30>1828,L30<=1908,N30=1,O30="yes"),C30/1000*2.15,IF(AND(L30>1828,L30<=1908,N30=1),C30/1000*1.95,IF(AND(L30>1828,L30<=1908,N30=2),C30/1000*1.82,IF(AND(L30>1828,L30<=1908,N30=2,O30="yes"),C30/1000*2.02,IF(AND(L30>1908,L30<=1965,N30=1,O30="yes"),C30/1000*2.16,IF(AND(L30>1908,L30<=1965,N30=1),C30/1000*1.96,IF(AND(L30>1908,L30<=1965,N30=2)*C30/1000*1.83,IF(AND(L30>1908,L30<=1965,N30=2,O30="yes"),C30/1000*2.03,IF(AND(L30>1965,L30<=1984,N30=1,O30="yes"),C30/1000*2.04,IF(AND(L30>1965,L30<=1984,N30=1),C30/1000*1.94,IF(AND(L30>1965,L30<=1984,N30=2),C30/1000*1.81,IF(AND(L30>1965,L30<=1984,N30=2,O30="yes"),C30/1000*2.01,IF(AND(L30>1984,L30<=2045,N30=1,O30="yes"),C30/1000*2.08,IF(AND(L30>1984,L30<=2045,N30=1),C30/1000*1.88,IF(AND(L30>1984,L30<=2045,N30=2),C30/1000*1.73,IF(AND(L30>1984,L30<=2045,N30=2,O30="yes"),C30/1000*1.93,IF(AND(L30>2045,L30<=2094,N30=1,O30="yes"),C30/1000*2.05,IF(AND(L30>2045,L30<=2094,N30=1),C30/1000*1.85,IF(AND(L30>2045,L30<=2094,N30=2)*C30/1000*1.72,IF(AND(L30>2045,L30<=2094,N30=2,O30="yes")*C30/1000*1.92,IF(AND(L30>2094,L30<=2290,N30=1,O30="yes"),C30/1000*2.03,IF(AND(L30>2094,L30<=2290,N30=1),C30/1000*1.83,IF(AND(L30>2094,L30<=2290,N30=2),C30/1000*1.7,IF(AND(L30>2094,L30<=2290,N30=2,O30="yes"),C30/1000*1.9,IF(AND(L30>2290,L30<=2592,N30=1,O30="yes"),C30/1000*2.01,IF(AND(L30>2290,L30<=2592,N30=1),C30/1000*1.81,IF(AND(L30>2290,L30<=2592,N30=2),C30/1000*1.68,IF(AND(L30>2290,L30<=2592,N30=2,O30="yes"),C30/1000*1.88,IF(AND(L30>2592,L30<=2714,N30=1,O30="yes"),C30/1000*2,IF(AND(L30>2592,L30<=2714,N30=1),C30/1000*1.8,IF(AND(L30>2592,L30<=2714,N30=2),C30/1000*1.67,IF(AND(L30>2592,L30<=2714,N30=2,O30="yes"),C30/1000*1.87,IF(AND(L30>2714,L30<=2896,N30=1,O30="yes"),C30/1000*1.99,IF(AND(L30>2714,L30<=2896,N30=1),C30/1000*1.78,IF(AND(L30>2714,L30<=2896,N30=2),C30/1000*1.66,IF(AND(L30>2714,L30<=2896,N30=2,O30="yes"),C30/1000*1.86,IF(AND(L30>2896,L30<=3175,N30=1,O30="yes"),C30/1000*1.97,IF(AND(L30>2896,L30<=3175,N30=1),C30/1000*1.77,IF(AND(L30>2896,L30<=3175,N30=2),C30/1000*1.64,IF(AND(L30>2896,L30<=3175,N30=2,O30="yes"),C30/1000*1.83,IF(AND(L30>3175,L30<=3258,N30=1,O30="yes"),C30/1000*1.965,IF(AND(L30>3175,L30<=3258,N30=1),C30/1000*1.765,IF(AND(L30>3175,L30<=3258,N30=2),C30/1000*1.635,IF(AND(L30>3175,L30<=3258,N30=2,O30="yes"),C30/1000*1.845,IF(AND(L30>3258,L30<=3362,N30=1,O30="yes"),C30/1000*1.96,IF(AND(L30>3258,L30<=3362,N30=1),C30/1000*1.76,IF(AND(L30>3258,L30<=3362,N30=2),C30/1000*1.63,IF(AND(L30>3258,L30<=3362,N30=2,O30="yes"),C30/1000*1.83,IF(AND(L30>3362,L30<=3722,N30=1,O30="yes"),C30/1000*1.9,IF(AND(L30>3362,L30<=3722,N30=1),C30/1000*1.7,IF(AND(L30>3362,L30<=3722,N30=2),C30/1000*1.57,IF(AND(L30>3362,L30<=3722,N30=2,O30="yes"),C30/1000*1.76,IF(AND(L30>3722,L30<=3916,N30=1,O30="yes"),C30/1000*1.96,IF(AND(L30>3722,L30<=3916,N30=1),C30/1000*1.76,IF(AND(L30>3722,L30<=3916,N30=2),C30/1000*1.63,IF(AND(L30>3722,L30<=3916,N30=2,O30="yes"),C30/1000*1.83,"do quote"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Now the problems I'm having is that it has all these different variables and it's supposed to shoot back numbers. and it does up until I get to a size of 1970, then instead of giving me numbers it comes back as "FALSE" for everything larger than 1970 until I get to a size of 3828 where it gives me this "#VALUE!", no Idea what's wrong here, or why it works for some of it and not all.
And the other thing I wanted to address was, as I said, I'm new to this, but this formula is GIGANTIC!! and you all seem to know your stuff, is there a way to shorten this to something more reasonable?

 

[lewisgmorris]

I don't even want to start looking at that. There must be a better way

Look at the end of the formula LOL ))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[konew1]

Explain in words what you are trying to achieve.

This bit looks wrong
,IF(AND(L30>2045,L30<=2094,N30=2)*C30/1000*1.72
Maybe ,IF(AND(L30>2045,L30<=2094,N30=2),C30/1000*1.72

On the assumption that N30 will never be anything other than 1, or 2, or 3. You can possible remove the condition L30>number from all AND functions after the first. That is because the first 4 conditions eliminate all situations where L30<=1740 there no need for condition if L30>1740 and all the subsequent AND conditions.

I would suggest a small table listing the start points of the L30 range down one column and the N30 values across the 3 columns then use index/match to find the multiplier

 

[DocAElstein]

I'm pretty new to excel, so I don't know exactly what's going on here or why, but I have this massive formula:


=IF(AND(L30<=1740,N30=1,O30="yes"),C30/1000*2.18,IF(AND(L3...........

.................. but this formula is GIGANTIC!! and you all seem to know your stuff, is there a way to shorten this to something more reasonable?

.. hi alxanderjon,

. Just a couple of small points.


. 1) I see you are using a nested IF Function to its maximum possible ( 64 ) IF bits. That would have been a strange coincidence if that originally was ideally wot was the number of conditions needed – it was most likely compromised because of the 64 limit. So that suggest that some look up type formula would be better. I used this nested IF type of solution a lot until I got educated first with VLOOKUP and then further with INDEX. Do not make my mistake. Put some effort in learning both VLOOKUP and INDEX before you go any further. It is worth it in the long run!
. (I assume you at least understand the IF (with AND) Function. – If not learn that before going ANY further!).


. 2) I do not think you have a difficult problem. I agree with konew1 and cgreene comments… It is just difficult to see and you need to make yourself a table showing the conditions and putting into words wot you want. That is simple, just very tedious and takes a while – but that is your job. Once you have done that it might be obvious that a better solution such as indicated in . 1) is better! And if you post your table and explanations it makes it easier for anyone further trying to help you


. 3) It can help to make formulas a bit easier to see, (at least short ones!!) especially when asking for help in this forum by using the MrExcel HTML spreadsheet Maker ( Download here: https://onedrive.live.com/?cid=8cffd...CE27E813%21189 instructions here: MrExcel HTML Maker . )
. It breaks the formula down a bit in colors and sometimes gives extra info about it (if for example it is an “Array Inputted thing”).

Yours would look like this:

Book1
	A	B
1	FALSCH
2
alxanderion

Cell Formulas
Range	Formula
A1	=IF(AND(L30<=1740,N30=1,O30="yes"),C30/1000*2.18,IF(AND(L30<=1740,N30=1),C30/1000*1.97,IF(AND(L30<=1740,N30=2),C30/1000*1.84,IF(AND(L30<=1740,N30=2,O30="yes"),C30/1000*2.05,IF(AND(L30>1740,L30<=1828,N30=1,O30="yes"),C30/1000*2.17,IF(AND(L30>1740,L30<=1828,N30=1),C30/1000*1.96,IF(AND(L30>1740,L30<=1828,N30=2),C30/1000*1.83,IF(AND(L30>1740,L30<=1828,N30=2,O30="yes"),C30/1000*2.04,IF(AND(L30>1828,L30<=1908,N30=1,O30="yes"),C30/1000*2.15,IF(AND(L30>1828,L30<=1908,N30=1),C30/1000*1.95,IF(AND(L30>1828,L30<=1908,N30=2),C30/1000*1.82,IF(AND(L30>1828,L30<=1908,N30=2,O30="yes"),C30/1000*2.02,IF(AND(L30>1908,L30<=1965,N30=1,O30="yes"),C30/1000*2.16,IF(AND(L30>1908,L30<=1965,N30=1),C30/1000*1.96,IF(AND(L30>1908,L30<=1965,N30=2)*C30/1000*1.83,IF(AND(L30>1908,L30<=1965,N30=2,O30="yes"),C30/1000*2.03,IF(AND(L30>1965,L30<=1984,N30=1,O30="yes"),C30/1000*2.04,IF(AND(L30>1965,L30<=1984,N30=1),C30/1000*1.94,IF(AND(L30>1965,L30<=1984,N30=2),C30/1000*1.81,IF(AND(L30>1965,L30<=1984,N30=2,O30="yes"),C30/1000*2.01,IF(AND(L30>1984,L30<=2045,N30=1,O30="yes"),C30/1000*2.08,IF(AND(L30>1984,L30<=2045,N30=1),C30/1000*1.88,IF(AND(L30>1984,L30<=2045,N30=2),C30/1000*1.73,IF(AND(L30>1984,L30<=2045,N30=2,O30="yes"),C30/1000*1.93,IF(AND(L30>2045,L30<=2094,N30=1,O30="yes"),C30/1000*2.05,IF(AND(L30>2045,L30<=2094,N30=1),C30/1000*1.85,IF(AND(L30>2045,L30<=2094,N30=2)*C30/1000*1.72,IF(AND(L30>2045,L30<=2094,N30=2,O30="yes")*C30/1000*1.92,IF(AND(L30>2094,L30<=2290,N30=1,O30="yes"),C30/1000*2.03,IF(AND(L30>2094,L30<=2290,N30=1),C30/1000*1.83,IF(AND(L30>2094,L30<=2290,N30=2),C30/1000*1.7,IF(AND(L30>2094,L30<=2290,N30=2,O30="yes"),C30/1000*1.9,IF(AND(L30>2290,L30<=2592,N30=1,O30="yes"),C30/1000*2.01,IF(AND(L30>2290,L30<=2592,N30=1),C30/1000*1.81,IF(AND(L30>2290,L30<=2592,N30=2),C30/1000*1.68,IF(AND(L30>2290,L30<=2592,N30=2,O30="yes"),C30/1000*1.88,IF(AND(L30>2592,L30<=2714,N30=1,O30="yes"),C30/1000*2,IF(AND(L30>2592,L30<=2714,N30=1),C30/1000*1.8,IF(AND(L30>2592,L30<=2714,N30=2),C30/1000*1.67,IF(AND(L30>2592,L30<=2714,N30=2,O30="yes"),C30/1000*1.87,IF(AND(L30>2714,L30<=2896,N30=1,O30="yes"),C30/1000*1.99,IF(AND(L30>2714,L30<=2896,N30=1),C30/1000*1.78,IF(AND(L30>2714,L30<=2896,N30=2),C30/1000*1.66,IF(AND(L30>2714,L30<=2896,N30=2,O30="yes"),C30/1000*1.86,IF(AND(L30>2896,L30<=3175,N30=1,O30="yes"),C30/1000*1.97,IF(AND(L30>2896,L30<=3175,N30=1),C30/1000*1.77,IF(AND(L30>2896,L30<=3175,N30=2),C30/1000*1.64,IF(AND(L30>2896,L30<=3175,N30=2,O30="yes"),C30/1000*1.83,IF(AND(L30>3175,L30<=3258,N30=1,O30="yes"),C30/1000*1.965,IF(AND(L30>3175,L30<=3258,N30=1),C30/1000*1.765,IF(AND(L30>3175,L30<=3258,N30=2),C30/1000*1.635,IF(AND(L30>3175,L30<=3258,N30=2,O30="yes"),C30/1000*1.845,IF(AND(L30>3258,L30<=3362,N30=1,O30="yes"),C30/1000*1.96,IF(AND(L30>3258,L30<=3362,N30=1),C30/1000*1.76,IF(AND(L30>3258,L30<=3362,N30=2),C30/1000*1.63,IF(AND(L30>3258,L30<=3362,N30=2,O30="yes"),C30/1000*1.83,IF(AND(L30>3362,L30<=3722,N30=1,O30="yes"),C30/1000*1.9,IF(AND(L30>3362,L30<=3722,N30=1),C30/1000*1.7,IF(AND(L30>3362,L30<=3722,N30=2),C30/1000*1.57,IF(AND(L30>3362,L30<=3722,N30=2,O30="yes"),C30/1000*1.76,IF(AND(L30>3722,L30<=3916,N30=1,O30="yes"),C30/1000*1.96,IF(AND(L30>3722,L30<=3916,N30=1),C30/1000*1.76,IF(AND(L30>3722,L30<=3916,N30=2),C30/1000*1.63,IF(AND(L30>3722,L30<=3916,N30=2,O30="yes"),C30/1000*1.83,"do quote"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Which you could again copy out as


=IF(AND(L30<=1740,N30=1,O30="yes"),C30/1000*2.18,IF(AND(L30<=1740,N30=1),C30/1000*1.97,IF(AND(L30<=1740,N30=2),C30/1000*1.84,IF(AND(L30<=1740,N30=2,O30="yes"),C30/1000*2.05,IF(AND(L30>1740,L30<=1828,N30=1,O30="yes"),C30/1000*2.17,IF(AND(L30>1740,L30<=1828,N30=1),C30/1000*1.96,IF(AND(L30>1740,L30<=1828,N30=2),C30/1000*1.83,IF(AND(L30>1740,L30<=1828,N30=2,O30="yes"),C30/1000*2.04,IF(AND(L30>1828,L30<=1908,N30=1,O30="yes"),C30/1000*2.15,IF(AND(L30>1828,L30<=1908,N30=1),C30/1000*1.95,IF(AND(L30>1828,L30<=1908,N30=2),C30/1000*1.82,IF(AND(L30>1828,L30<=1908,N30=2,O30="yes"),C30/1000*2.02,IF(AND(L30>1908,L30<=1965,N30=1,O30="yes"),C30/1000*2.16,IF(AND(L30>1908,L30<=1965,N30=1),C30/1000*1.96,IF(AND(L30>1908,L30<=1965,N30=2)*C30/1000*1.83,IF(AND(L30>1908,L30<=1965,N30=2,O30="yes"),C30/1000*2.03,IF(AND(L30>1965,L30<=1984,N30=1,O30="yes"),C30/1000*2.04,IF(AND(L30>1965,L30<=1984,N30=1),C30/1000*1.94,IF(AND(L30>1965,L30<=1984,N30=2),C30/1000*1.81,IF(AND(L30>1965,L30<=1984,N30=2,O30="yes"),C30/1000*2.01,IF(AND(L30>1984,L30<=2045,N30=1,O30="yes"),C30/1000*2.08,IF(AND(L30>1984,L30<=2045,N30=1),C30/1000*1.88,IF(AND(L30>1984,L30<=2045,N30=2),C30/1000*1.73,IF(AND(L30>1984,L30<=2045,N30=2,O30="yes"),C30/1000*1.93,IF(AND(L30>2045,L30<=2094,N30=1,O30="yes"),C30/1000*2.05,IF(AND(L30>2045,L30<=2094,N30=1),C30/1000*1.85,IF(AND(L30>2045,L30<=2094,N30=2)*C30/1000*1.72,IF(AND(L30>2045,L30<=2094,N30=2,O30="yes")*C30/1000*1.92,IF(AND(L30>2094,L30<=2290,N30=1,O30="yes"),C30/1000*2.03,IF(AND(L30>2094,L30<=2290,N30=1),C30/1000*1.83,IF(AND(L30>2094,L30<=2290,N30=2),C30/1000*1.7,IF(AND(L30>2094,L30<=2290,N30=2,O30="yes"),C30/1000*1.9,IF(AND(L30>2290,L30<=2592,N30=1,O30="yes"),C30/1000*2.01,IF(AND(L30>2290,L30<=2592,N30=1),C30/1000*1.81,IF(AND(L30>2290,L30<=2592,N30=2),C30/1000*1.68,IF(AND(L30>2290,L30<=2592,N30=2,O30="yes"),C30/1000*1.88,IF(AND(L30>2592,L30<=2714,N30=1,O30="yes"),C30/1000*2,IF(AND(L30>2592,L30<=2714,N30=1),C30/1000*1.8,IF(AND(L30>2592,L30<=2714,N30=2),C30/1000*1.67,IF(AND(L30>2592,L30<=2714,N30=2,O30="yes"),C30/1000*1.87,IF(AND(L30>2714,L30<=2896,N30=1,O30="yes"),C30/1000*1.99,IF(AND(L30>2714,L30<=2896,N30=1),C30/1000*1.78,IF(AND(L30>2714,L30<=2896,N30=2),C30/1000*1.66,IF(AND(L30>2714,L30<=2896,N30=2,O30="yes"),C30/1000*1.86,IF(AND(L30>2896,L30<=3175,N30=1,O30="yes"),C30/1000*1.97,IF(AND(L30>2896,L30<=3175,N30=1),C30/1000*1.77,IF(AND(L30>2896,L30<=3175,N30=2),C30/1000*1.64,IF(AND(L30>2896,L30<=3175,N30=2,O30="yes"),C30/1000*1.83,IF(AND(L30>3175,L30<=3258,N30=1,O30="yes"),C30/1000*1.965,IF(AND(L30>3175,L30<=3258,N30=1),C30/1000*1.765,IF(AND(L30>3175,L30<=3258,N30=2),C30/1000*1.635,IF(AND(L30>3175,L30<=3258,N30=2,O30="yes"),C30/1000*1.845,IF(AND(L30>3258,L30<=3362,N30=1,O30="yes"),C30/1000*1.96,IF(AND(L30>3258,L30<=3362,N30=1),C30/1000*1.76,IF(AND(L30>3258,L30<=3362,N30=2),C30/1000*1.63,IF(AND(L30>3258,L30<=3362,N30=2,O30="yes"),C30/1000*1.83,IF(AND(L30>3362,L30<=3722,N30=1,O30="yes"),C30/1000*1.9,IF(AND(L30>3362,L30<=3722,N30=1),C30/1000*1.7,IF(AND(L30>3362,L30<=3722,N30=2),C30/1000*1.57,IF(AND(L30>3362,L30<=3722,N30=2,O30="yes"),C30/1000*1.76,IF(AND(L30>3722,L30<=3916,N30=1,O30="yes"),C30/1000*1.96,IF(AND(L30>3722,L30<=3916,N30=1),C30/1000*1.76,IF(AND(L30>3722,L30<=3916,N30=2),C30/1000*1.63,IF(AND(L30>3722,L30<=3916,N30=2,O30="yes"),C30/1000*1.83,"do quote"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))


. You could then copy this colorful one and paste it, for example in this form in WORD to assist in your “break down” or “breaking apart “ etc , and analysis purpose to produce those tables we talked about.
Alan
P,s. Welcome to the Board by the way.

………………………………

Hi cgreene..
. Great that you try to answer these threads—every bit helps I find. Keep it up. But if you just have a short comment as you did then wait a while before doing that. I say that because many regulars, moderators and profis who often give the best answers click later when they have time on the zero response button to pick up unanswered threads. (they are all amazingly helpful and busy on the forums). After you answer the thread, the thead no longer shows up as zero response so you wipe out the OP’s chance of a good response from a pro. (I am not a pro – just a beginner like I think you). I suggest waiting at least a Day unless you have a good full answer, or are prepared to follow it up and do that. But that is just my opinion. As I say I am a beginner myself trying to use the forum most effectively and contributing the little that I can! I keep getting moaned at by the moderators
Alan.


………………………………………

Hi finally to anyone else looking in….
. I have a follow up question. I made the mistake of using nested IF to its 64 limit before I got educated and so have files full with massive formulas of exactly the form from the OP. I intend sometime trying a VBA code or similar to do an automatic conversion of such a formula to an appropriate LookUpTable and maybe even further to give the equivalent VLOOKUP or INDEX, or INDEX with match formula. …
…

…Anyone know if something like that has already been done??
.. If not I will post it here when I get time to do it. Or start a new Thread on it or I will be moaned at again for “Hijacking a thread!” (Probably rightfully so!!)
Alan.

 

[konew1]

Here is an example of what all the replies have suggested. Note - I got tired of trying to unravel the multiplier factors from the giant formula so I made up numbers to fill the panel. Put in the real numbers and check carefully. Check what happens if L30 exceeds the listed range, of N30 has values other than 1 or 2, and if O30 has something other than YES

Excel Workbook
	C	D	H	I	J	K	L	M	N	O
30	1000						1990		2	yes
31		3.1
32
33
34
35
36
37			L30	1YES	2YES	1	2
38			0	2.18	2.05	1.97	1.84
39			1740	2.17	2.04	1.96	1.83
40			1828	1.1	1.2	1.3	1.4
41			1908	2	2.1	2.2	2.3
42			1965	2.04	2.01	1.88	1.81
43			1984	3	3.1	3.2	3.3
44			2045	4	4.1	4.2	4.3
45			2094	5	5.1	5.2	5.3
46			2290	6	6.1	6.2	6.3
47			2592	7	7.1	7.2	7.3
48			2714	8	8.1	8.2	8.3
49			2896	9	9.1	9.2	9.3
50			3175	1	1.4	1.5	1.6
51			3258	2	2.4	2.5	2.6
52			3362	3	3.4	3.5	3.6
Sheet1

 

[alxanderjon]

you guys are awesome Thank you!!

 

[DocAElstein]

you guys are awesome Thank you!!

. You are welcome. I except the konew1 info and formula may have the solution for you. – (Such a formula is still a bit difficult for me to try to work through and understand!)
. But a small help maybe: - as regards tediously breaking down such giant formulas into the conditions (in your case maximum 64!!), a small help is in order, after all that is wot VBA and such is for – to take away the tedious repetitive work) maybe.
. I got fed up doing that and knocked up a code to do it which Snakehips then did a lot better here:
http://www.mrexcel.com/forum/excel-questions/814504-formula-too-long-3.html
. Applying then one of those Codes to your giant formula gives a condition listing, which looks like this: **(I only post the first bit here for Post readability!!)

Book1
	AA
20	Split IFs >>> =IF(AND(L30<=1740,N30=1,O30="yes"),C30/1000*2.18,IF(AND(L30<=1740,N30=1),C30/1000*1.97,IF(AND(L30<=1740,N30=2),C30/1000*1.84,IF(AND(L30<=1740,N30=2,O30="yes"),C30/1000*2.05,IF(AND(L30>1740,L30<=1828,N30=1,O30="yes"),C30/1000*2.17,IF(AND(L30>1740,L30<=1828,N30=1),C30/1000*1.96,IF(AND(L30>1740,L30<=1828,N30=2),C30/1000*1.83,IF(AND(L30>1740,L30<=1828,N30=2,O30="yes"),C30/1000*2.04,IF(AND(L30>1828,L30<=1908,N30=1,O30="yes"),C30/1000*2.15,IF(AND(L30>1828,L30<=1908,N30=1),C30/1000*1.95,IF(AND(L30>1828,L30<=1908,N30=2),C30/1000*1.82,IF(AND(L30>1828,L30<=1908,N30=2,O30="yes"),C30/1000*2.02,IF(AND(L30>1908,L30<=1965,N30=1,O30="yes"),C30/1000*2.16,IF(AND(L30>1908,L30<=1965,N30=1),C30/1000*1.96,IF(AND(L30>1908,L30<=1965,N30=2)*C30/1000*1.83,IF(AND(L30>1908,L30<=1965,N30=2,O30="yes"),C30/1000*2.03,IF(AND(L30>1965,L30<=1984,N30=1,O30="yes"),C30/1000*2.04,IF(AND(L30>1965,L30<=1984,N30=1),C30/1000*1.94,IF(AND(L30>1965,L30<=1984,N30=2),C30/1000*1.81,IF(AND(L30>1965,L30<=1984,N30=2,O30="yes"),C30/1000*2.01,IF(AND(L30>1984,L30<=2045,N30=1,O30="yes"),C30/1000*2.08,IF(AND(L30>1984,L30<=2045,N30=1),C30/1000*1.88,IF(AND(L30>1984,L30<=2045,N30=2),C30/1000*1.73,IF(AND(L30>1984,L30<=2045,N30=2,O30="yes"),C30/1000*1.93,IF(AND(L30>2045,L30<=2094,N30=1,O30="yes"),C30/1000*2.05,IF(AND(L30>2045,L30<=2094,N30=1),C30/1000*1.85,IF(AND(L30>2045,L30<=2094,N30=2)*C30/1000*1.72,IF(AND(L30>2045,L30<=2094,N30=2,O30="yes")*C30/1000*1.92,IF(AND(L30>2094,L30<=2290,N30=1,O30="yes"),C30/1000*2.03,IF(AND(L30>2094,L30<=2290,N30=1),C30/1000*1.83,IF(AND(L30>2094,L30<=2290,N30=2),C30/1000*1.7,IF(AND(L30>2094,L30<=2290,N30=2,O30="yes"),C30/1000*1.9,IF(AND(L30>2290,L30<=2592,N30=1,O30="yes"),C30/1000*2.01,IF(AND(L30>2290,L30<=2592,N30=1),C30/1000*1.81,IF(AND(L30>2290,L30<=2592,N30=2),C30/1000*1.68,IF(AND(L30>2290,L30<=2592,N30=2,O30="yes"),C30/1000*1.88,IF(AND(L30>2592,L30<=2714,N30=1,O30="yes"),C30/1000*2,IF(AND(L30>2592,L30<=2714,N30=1),C30/1000*1.8,IF(AND(L30>2592,L30<=2714,N30=2),C30/1000*1.67,IF(AND(L30>2592,L30<=2714,N30=2,O30="yes"),C30/1000*1.87,IF(AND(L30>2714,L30<=2896,N30=1,O30="yes"),C30/1000*1.99,IF(AND(L30>2714,L30<=2896,N30=1),C30/1000*1.78,IF(AND(L30>2714,L30<=2896,N30=2),C30/1000*1.66,IF(AND(L30>2714,L30<=2896,N30=2,O30="yes"),C30/1000*1.86,IF(AND(L30>2896,L30<=3175,N30=1,O30="yes"),C30/1000*1.97,IF(AND(L30>2896,L30<=3175,N30=1),C30/1000*1.77,IF(AND(L30>2896,L30<=3175,N30=2),C30/1000*1.64,IF(AND(L30>2896,L30<=3175,N30=2,O30="yes"),C30/1000*1.83,IF(AND(L30>3175,L30<=3258,N30=1,O30="yes"),C30/1000*1.965,IF(AND(L30>3175,L30<=3258,N30=1),C30/1000*1.765,IF(AND(L30>3175,L30<=3258,N30=2),C30/1000*1.635,IF(AND(L30>3175,L30<=3258,N30=2,O30="yes"),C30/1000*1.845,IF(AND(L30>3258,L30<=3362,N30=1,O30="yes"),C30/1000*1.96,IF(AND(L30>3258,L30<=3362,N30=1),C30/1000*1.76,IF(AND(L30>3258,L30<=3362,N30=2),C30/1000*1.63,IF(AND(L30>3258,L30<=3362,N30=2,O30="yes"),C30/1000*1.83,IF(AND(L30>3362,L30<=3722,N30=1,O30="yes"),C30/1000*1.9,IF(AND(L30>3362,L30<=3722,N30=1),C30/1000*1.7,IF(AND(L30>3362,L30<=3722,N30=2),C30/1000*1.57,IF(AND(L30>3362,L30<=3722,N30=2,O30="yes"),C30/1000*1.76,IF(AND(L30>3722,L30<=3916,N30=1,O30="yes"),C30/1000*1.96,IF(AND(L30>3722,L30<=3916,N30=1),C30/1000*1.76,IF(AND(L30>3722,L30<=3916,N30=2),C30/1000*1.63,IF(AND(L30>3722,L30<=3916,N30=2,O30="yes"),C30/1000*1.83,"do quote"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
21	(AND(L30<=1740,N30=1,O30="yes"),C30/1000*2.18,
22	(AND(L30<=1740,N30=1),C30/1000*1.97,
23	(AND(L30<=1740,N30=2),C30/1000*1.84,
24	(AND(L30<=1740,N30=2,O30="yes"),C30/1000*2.05,
25	(AND(L30>1740,L30<=1828,N30=1,O30="yes"),C30/1000*2.17,
26	(AND(L30>1740,L30<=1828,N30=1),C30/1000*1.96,
Tabelle1

. **If that is helpful and you have problems going through that Thread and the codes then get back and I can post the full conditions here.

. Thanks for the reply and let us know if konew1 stuff does the trick!
Alan

 

[DocAElstein]

….. and just for fun while I was at it..

..did a bit of further playing around with the code to pick out each bit …..

Book1
	AD	AE
1	(AND(L30<=1740,N30=1,O30="yes")	C30/1000*2.18
2	(AND(L30<=1740,N30=1)	C30/1000*1.97
3	(AND(L30<=1740,N30=2)	C30/1000*1.84
4	(AND(L30<=1740,N30=2,O30="yes")	C30/1000*2.05
5	(AND(L30>1740,L30<=1828,N30=1,O30="yes")	C30/1000*2.17
Tabelle1

… Maybe helpful for copying columns to put into LookUpTables etc. But the code gets a bit messy and may not cope with every formula form..

 

[DocAElstein]

….. and just for fun while I was at it..

..did a bit of further playing around with the code to pick out each bit ......

… or rather

Book1
	AD	AE
1	AND(L30<=1740,N30=1,O30="yes")	C30/1000*2.18
2	AND(L30<=1740,N30=1)	C30/1000*1.97
3	AND(L30<=1740,N30=2)	C30/1000*1.84
4	AND(L30<=1740,N30=2,O30="yes")	C30/1000*2.05
5	AND(L30>1740,L30<=1828,N30=1,O30="yes")	C30/1000*2.17
Tabelle1

… just have to be a bit careful (as I was not ) to get the right bit. But I think you get the point....