» Nesting Formulas

can this apply to any "countif" or "IF", ETC

I dont believe this is a nested formular but I cant identify how it works. This takes the total monthly value and asks what percentage it is to the total quarterly value.
example ...monly/qtr. =b12/$e12$b12

I've been working on this formula for hours and am very frustrated. The formulas works when cell 3 equals M. When cell 3 does not equal M, I want the formula to SUM(G333,H332)-H334 but it doesn't work and instead returns the VALUE error. Following is the entire formula. Please help!

=(IF(H$3="M",IF(H330>0,SUM(G333,G332,G332,H332)-H334),(IF(H$3="M",IF(H330=0,SUM(G333,G332,G332)-H334),SUM((G333,H332)-H334)))))

Can anyone help please. I want to do insert an IF function where one of the conditions is that a cell is not equal to zero. Is it possible to do that? Is there a keyboard character which can do it or can I create some sort of Macro? I have the XP Works version of EXCEL

Thanks

Desmond, the condition you want - is not equal to "0" means the cell should not hold a value of 0. In other words, it can have a value greater than or less than 0. For this just use the characters for less than and greater than together like this <> no space in between.
This will surely give you the results.

I have a table of Universal Beam Capacities, which when you specify the length of the beam, and the capacity it is required to withstand, the result should be the Beam Designation. I have setup a LOOKUP formula to select the Designation based upon the criteria, however Excel always selects the lower value of the range of capacities - I need the formula to return the higher value. Do you know how to force Excel to select the higher value within a range?
The other option is to somehow force Excel to always jump to the next cell to return the higher value - do you know of a way?

Pauline:

Could you give me some samply data so that I can work it out for you?

Thanks

Subbu

Thank you for your reply. A sample of the table data is below. I have had to use LOOKUP, as the result required is in the first column. The aim is to enter in the length of the beam (across the top of the table), and then a capacity, which will return the next available (higher) capacity which returns the Designation of the Beam. For example, if the Length is 3 metres, and the capacity is 19, the result should be 180UB 19.0

TABLE 5.3-5
UNIVERSAL BEAMS
DESIGN MOMENT CAPACITIES
Designation kNm kN 2 3
150UB 14.0 29.3 130 16.8 12
180UB 16.1 39.8 135 24.8 17.7
150UB 18.0 28.9 161 24.2 18.4
180UB 19.0 45.2 151 29 21.2
200UB 18.2 51.8 154 33.6 23.8

The table would also need to be sorted by the required length column, in ascending order.

Thank you for your help

Pauline

Hi Pauline,

The table got a bit mangled in the HTML rendering so I may have the wrong end of the stick here.

However, you might want to try using the MATCH and INDEX functions together to do what you want.

The problem with VLOOKUP is that, even with non exact matching enabled (using the TRUE parameter) it will return the largest value that is LESS than your search value.

This might be useful in many circumstances, but for engineering, where you need to be 'safe' it is not ideal.

MATCH can be set to go either way, so it will do what you need I think, but it then needs to be combined with the INDEX function to turn the relative position that it returns, into a value from the table.

Does that help?

Alan.

Alan

Thank you for your reply.

Sorry about the Table format - hopefully, this will be better, with the TABS replaced by Spaces.

Do you know how I can incorporate the INDEX & MATCH arguments into my LOOKUP formula? Does it need to be written in VBA?

I have tried some combinations of formulas within the LOOKUP, but I keep getting VALUE errors.

The setup I have, allows the user to enter the length and capacity. To select the respective column, which represents the length, I have named each column range, and setup in a table. Once the column is selected, I have the following formula to lookup the value:

LOOKUP(CAPACITY,RANGE,Designation)

e.g. Capacity = 19, Length = 3, returning 150UB 18.0, as the answer. but it should be 180UB 19.0

Designation | kNm |kN |2 |3
150UB 14.0 | 29.3 |130 |16.8 |12
180UB 16.1 | 39.8 |135 |24.8 |17.7
150UB 18.0 | 28.9 |161 |24.2 |18.4
180UB 19.0 | 45.2 |151 |29 |21.2
200UB 18.2 | 51.8 |154 |33.6 |23.8

Look forward to your reply

Thanks

Pauline

Hi Pauline,

What does the 2 and 3 in your column headings represent?

Are there only two possible 'lengths'?

What if the user put in, say, Capacity = 19, Length = 3.5

What answer would you expact the table to return to you?

I think we are nearly there, and I should be able to give you a formula that works.

Alan.

Alan

This table is only a sample of the data - the columns represent the length of the beam required, so in this sample, 2 represents 2 metres in length, etc, with the lengths up to 16 metres, with 28 possible designations. The lengths are represented as whole numbers only.

The lengths are pretty much standard, with the variations occuring in the capacities, and hence the need to select the higher capacity value.

From the table provided in the earlier email, the result of the following e.g. Capacity = 19, Length = 3, returns under LOOKUP "150UB 18.0" as the answer, but it should be "180UB 19.0".

I hope this explains the objective of the formula

Thank you again for your help

Regards

Pauline

Hi Pauline,

I think I understand now.

Basically you are saying that you want to across to the column that contains the 'Length' input as denoted in your table as the columns headed 2 and 3, then down until you find a number greater than the 'Capacity' input.

You then go back across to the left hand most column, and read off the 'Designation' ?

Is that correct? If so:

Assume your (reduced) data set above is in A1:E6.

I am also assuming that all the numbers are entered as numbers (rather than text), and that the 'Length' is entered as an integer that exists in the table as per your previous post (this could be error checked at the input area).

The following should work:

{=INDEX(A1:A6,5-MATCH(Capacity,MMULT({0,0,0,0,1;0,0,0,1,0;0,0,1,0,0;0,1,0,0,0;1,0,0,0,0},OFFSET(A1,1,MATCH(Length,A1:E1,0)-1,5,1)),-1))}

This is an array formula. Enter it without the braces, using Shift-Ctrl-Enter.

I realise that it is a bit daunting, but I think it works!

To generalise though, you will need to work your way through the formula since it requires you to replace certain entries with the actual dimensions of your data table (I used A1:E6 in this example, which made the actual data set 5 rows).

Notes:

A1:A6 will need to extend down to the bottom of your data range

5-MATCH(...... will have to be changed for the actual rows of data you have.

The '5' inside the MATCH (inside the OFFSET) will also need ot be changed acording to your actual data set size.

The matrix that is multiplied by your Length column of data to reverse the order needs to be properly sized to match the data length. That will be best done by 'generating' the matrix rather than typing it in (else the formula will probably run over the haracter limit in Excel).

For all of the above adjustments, it would be best to calculate the answers elsewhere in your model, and then return them to the formula(e) so that if the data range changes size, you don't have to fiddle with it constantly.

I would suggest you actually split the formula into at least three cells (say, Z1, Z2, Z3):

Z1: =MATCH(Length,A1:E1,0)

Z2: {=5-MATCH(Capacity,MMULT({0,0,0,0,1;0,0,0,1,0;0,0,1,0,0;0,1,0,0,0;1,0,0,0,0},OFFSET(A1,1,Z1-1,5,1)),-1)}

Z3: INDEX(A1:A6,Z2)


Further comment:

I don't like the matrix entered directly as I have above, but I cannot think of an easy way to generate it using a formula right now. It is probably easy, but it is midnight here!

If this is a one-off issue, you could just set it up in a blank part of your spreadsheet, and refer to it there (using your variable dimensions) I guess, but that is not very elegant.

Hope that helps - at least we are getting there I think!

Alan.

Hi Pauline,

You can generate the array using the following formula:

If A1 contains the size of the array (equal to the number of rows of data you have), then the following produces the array you need in the formula above:

{=(ROW(INDIRECT("1:"&A1))+TRANSPOSE(ROW(INDIRECT("1:"&A1)))=(A1+1))*1}

I must acknowledge the assistance of J.E. McGimpsey:

http://www.excelforum.com/t116751-s

OR if you prefer to use a news client:

news://news.microsoft.com/Alan.t5oiz@excelforum.com

Does that solve it for you Pauline?

Alan.