HOW TO MAKE IF STATMENTS INTO ARRAYS

[GoonSqd]

hey there, im trying to change a co-workers if statements into an array if possible
im trying to validate that If Colum AA is 1 or greater then to check if Colum AQ is between 7.3 and 8.3 and if its anything else to check Colum y and if there is a date then colum AQ should be zero, if there isnt then check colum x and make that the value for colum AQ

 

[awoohaw]

You're asking for column AQ data to be read and then possibly changed. It can't be a value (say between 7.3 and 8.3) and then have a formula inside that cell to change it to zero if it isn't.

Are you asking to do this in VBA and not excel formulas?

 

[GoonSqd]

You're asking for column AQ data to be read and then possibly changed. It can't be a value (say between 7.3 and 8.3) and then have a formula inside that cell to change it to zero if it isn't.

Are you asking to do this in VBA and not excel formulas?

im trying to see if its possible to make an array to replace the if then formulas on the right hand side, in order to keep the file size down.

 

[awoohaw]

you can't have two thingsin a cell, you are asking for AQ (that already has a value) to have a formula in AQ that changes its value.

What cell is the IF statement in? Your original request reads like you want it in AQ

You can help people help you by giving samples of your worksheets by using the xl2bb code (link below).

 

[awoohaw]

I don't know how you can overwrite data in AQ.
Here is my interpretation of your IF statements:

Book1
	B	C	D	E	F	G
1
2	Conditions				Result
3	Col AA	Col Y	Col X	Col AQ	In What cell?
4	"P"			> 7.3 & < 8.3	Value of AQ
5		Not Blank		0	0
6			Not Blank		Value of X
7	"C"			0	0
8
Sheet5

 

[GoonSqd]

I don't know how you can overwrite data in AQ.
Here is my interpretation of your IF statements:

Book1
	B	C	D	E	F	G
1
2	Conditions				Result
3	Col AA	Col Y	Col X	Col AQ	In What cell?
4	"P"			> 7.3 & < 8.3	Value of AQ
5		Not Blank		0	0
6			Not Blank		Value of X
7	"C"			0	0
8
Sheet5

So essentially im trying to find the best way to make a new column that will first check or validates the logic that follows column "AA" which repersent a price increase 1= yes 0=no, Then i would like it to check column "AQ" and make sure the price increase is between 7.3-8.3 and if its not then to check column Y because thats when the price increase will happen and if has a number in column "x" then that means the price increase is capped at that value for the customer and should be represented in "column "AQ". I hope this makes sense any help is greatly appreciated.

 

[awoohaw]

Okay I'm starting to understand. Please fill in the questions. In column Y what are you supposed to do with a date? Is this a date the price increase is to take effect or end? How do you want that reflected in the calculation?

Book1
	A	B	C	D	E	F
3
4	Conditions		Increase Date?	Price Cap?		Result
5	Col AA	Col AQ	Col Y	Col X		In What cell?
6	1	> 7.3 & < 8.3	End of Conditions calculate now			What value to see?
7	1	NOT(> 7.3 & < 8.3)	Has a date?	End of Conditions calculate now		What value to see?
8	1	NOT(> 7.3 & < 8.3)	Has a date?	Cap amount	End of Conditions calculate now	What value to see?
9	0	End of Conditions calculate now				What value to see?
10
Sheet1

 

[GoonSqd]

Okay I'm starting to understand. Please fill in the questions. In column Y what are you supposed to do with a date? Is this a date the price increase is to take effect or end? How do you want that reflected in the calculation?

Book1
	A	B	C	D	E	F
3
4	Conditions		Increase Date?	Price Cap?		Result
5	Col AA	Col AQ	Col Y	Col X		In What cell?
6	1	> 7.3 & < 8.3	End of Conditions calculate now			What value to see?
7	1	NOT(> 7.3 & < 8.3)	Has a date?	End of Conditions calculate now		What value to see?
8	1	NOT(> 7.3 & < 8.3)	Has a date?	Cap amount	End of Conditions calculate now	What value to see?
9	0	End of Conditions calculate now				What value to see?
10
Sheet1

The Date is when when the price increase will take effect, so if there is a date and there is no cap then i need it to return "correct" or True" i guess all i need is it to return a validation of good or bad with those set rules.

 

[GoonSqd]

The Date is when when the price increase will take effect, so if there is a date and there is no cap then i need it to return "correct" or True" i guess all i need is it to return a validation of good or bad with those set rules.

im Sorry i need it to check that if there is no date then AQ should read zero and validate that then return with correct or not

 

[GoonSqd]

Okay I'm starting to understand. Please fill in the questions. In column Y what are you supposed to do with a date? Is this a date the price increase is to take effect or end? How do you want that reflected in the calculation?

Book1
	A	B	C	D	E	F
3
4	Conditions		Increase Date?	Price Cap?		Result
5	Col AA	Col AQ	Col Y	Col X		In What cell?
6	1	> 7.3 & < 8.3	End of Conditions calculate now			What value to see?
7	1	NOT(> 7.3 & < 8.3)	Has a date?	End of Conditions calculate now		What value to see?
8	1	NOT(> 7.3 & < 8.3)	Has a date?	Cap amount	End of Conditions calculate now	What value to see?
9	0	End of Conditions calculate now				What value to see?
10
Sheet1