Excel 2007 : IF multiplier

**Martin Chamberlin** · 06-04-2011, 02:13 PM

This is my code and heres a challenge.

=IF($BE$134=BE123,"A",IF(AND(BE123>=$BF$134,BE123<=$BH$134),"A",IF(AND(BE123>=$BF$134+$A$101,BE123<=$BH$134+$A$101),"A",IF(AND(BE123>=$BF$134+$A$101*2,BE123<=$BH$134+$A$101*2),"A",IF(AND(BE123>=$BF$134+$A$101*3,BE123<=$BH$134+$A$101*3),"A","")))))

One of you experts will see that it is a code which is entering a character A into cells at x1, x2, x3 a value etc.

I want to go to x 30 or x70 or x100 etc. The code will work but is basic and will take too long. Does anyone know a simple way of using a simple code to x1,x2,x3,x4,x5,to x30 and upwards ????

Thanks

Chambo

**MarvinP** · 06-04-2011, 02:29 PM

Hi Chambo,

Your formula makes me wonder if VLookup can't do it. If not then how about using Rows() or Columns() as a variable to help in your formula.

If you attach a sample workbook it is much easier to understand the problem and get closer to a solution.
To attach a sample: Click on "Go Advanced" below the message box and then on the Paper Clip Icon above the Advanced Message area.

**Martin Chamberlin** · 06-04-2011, 03:26 PM

Originally Posted by MarvinP

Hi Chambo,

Your formula makes me wonder if VLookup can't do it. If not then how about using Rows() or Columns() as a variable to help in your formula.

If you attach a sample workbook it is much easier to understand the problem and get closer to a solution.
To attach a sample: Click on "Go Advanced" below the message box and then on the Paper Clip Icon above the Advanced Message area.

Thanks for the quick response. I will send you an excel file in morning.

Regards

Chambo

**Martin Chamberlin** · 06-05-2011, 02:21 AM

Here we go. The objective is to put the actual production value into F35 at todays date.

The new targets A then resets itself up the column as you will see when you enter a value into F 35.

My challenge, I may want to extend the lookahead to say 6 months, and add operators.

I need a simpler code to multiply x1,x2,to say x20 etc and then copy and paste over quickly to add more operators. Any help will be appreciated.

Thanks

Chambo

**MarvinP** · 06-05-2011, 11:34 AM

Hi Martin,

Find the attached where I've created a helper column (Column M) of values you are looking for. It is built using your input numbers. The formula uses Row() for the multiplier of A5. Then in Column L is a VLookup to see if the value in Column F matches any of the values in the Helper Column. If there is a match it returns an A. If no match it returns a blank.

You can extend the helper column down as many rows as you need and expand the VLookup Array range to the full table.

I think this is what you are looking for.

To test it change the value in A5 and/or in F35 and see if your answers and mine match.

Hope that helps.

**Martin Chamberlin** · 06-05-2011, 12:14 PM

You guys amaze me. I dont know how it works, the formula is looking at cells M4 to M7 and coming up with the right placement of A in the column.

Have a look at how my tolerance works(data input B12 and B13). If you can replicate this also then we are there.

Many thanks

Martin

**MarvinP** · 06-05-2011, 01:07 PM

Hey Martin,

"You Guys" should be me! Although many of the guru's could have spent time to understand this also.

See the attached where I needed two more helper columns. I changed the VLookup formulas to Match as they are a little shorter.

Study a while with what is in the attachment to put it into your work.

I simply created 3 tables of values to look for. The first was the beginning value plus multiples of that 7000 number cell. The second table was the first table plus Tot- and the third table was plus Tot+. Then the formula that produces an A or not was simply looking for a match in any of the tables to the number in the F column.

Hope this gets you on your way.

The hardest part of this problem was understanding it. Why you need it is beyond me.

**Martin Chamberlin** · 06-05-2011, 03:53 PM

Marvin, your a genius. Big thanks, problem solved.

Martin

Originally Posted by MarvinP

Hey Martin,

"You Guys" should be me! Although many of the guru's could have spent time to understand this also.

See the attached where I needed two more helper columns. I changed the VLookup formulas to Match as they are a little shorter.

Study a while with what is in the attachment to put it into your work.

I simply created 3 tables of values to look for. The first was the beginning value plus multiples of that 7000 number cell. The second table was the first table plus Tot- and the third table was plus Tot+. Then the formula that produces an A or not was simply looking for a match in any of the tables to the number in the F column.

Hope this gets you on your way.

The hardest part of this problem was understanding it. Why you need it is beyond me.