EXCEL - ELEGANT FORMULAS

Question

EXCEL - ELEGANT FORMULAS

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

Hello, I need help to create a more elegant formula than the one below:

=IF(OR(AND(C11>=3302000;C11<=3303999);AND(C11>=3602000;C11<=3603999));
$B$3;IF(OR(AND(C11>=3402000;C11<=3403999);AND(C11>=3702000;C11<=3703999));
$B$4;IF(OR(AND(C11>=3121000;C11<=3121999);AND(C11>=3181000;C11<=3191999);
AND(C11>=3131000;C11<=3137999));$B$5;IF(OR(AND(C11>=3122000;C11<=3122999);
AND(C11>=3281000;C11<=3291999);AND(C11>=3231000;C11<=3237999));
$B$6;IF(C11="N/A";"N/A";C11)))))

What the formula needs to do is see if the value of C11 is in the image value envelopes, and if it is, put the name of the envelope in cell B11.

Suggestions? Thank you.

excel

asked by anonymous 31.01.2017 / 16:40

2 answers

Obtain authenticated user in constructor Is it possible to develop websites with C / C ++?

score 0 · Answer 1

My first suggestion is that you separate each Envelope Range value into 2 cells. For example, in cell C3 you have placed 3302000-3303999. This would separate into a cell with 3302000 and another cell with 3303999. That way you do not have to write the values manually in your formula. This leaves the formula "smart" for future changes in values. The way you did, every time you change some value in the search ranges, you will have to tweak your formula.

My other suggestion, as its formula is long and complex (I have worked several times like this and know how annoying and easy to get lost), is to work as a public function using VBA. It gets more organized. You call the function in the cell and pass some parameter if necessary. All processing is organized within the VBA code.

score 0 · Answer 2

Hello, I could separate, as suggested by your colleague. A simple way to separate is by using the let or the right and opening other columns. In your case you will have to open the columns from "F" to "K" and put the formulas interleaved, for example:

'Na coluna "F"
=left(C3;7)

'Na coluna "G"
=right(C3;7)

'Na coluna "H"
=left(D3;7)

'Na coluna "I"
=right(D3;7)

'Na coluna "J"
=left(E3;7)

'Na coluna "K"
=right(E3;7)

Then you should copy the formulas down. All these columns must have the same name: "Load Case ID" - on line 2

Then you make an index match from cell C11 down, the formula is this:

=index($B$3:$B$6;;match(B11;$B$2:$K$6;0))

This should fix what you need, your only concern will have to be to sort out the formula each time one of the two tables has rows added to them. because you will have to change the final range of the formula.