Formula based on certain cells data

[T3as3r22]

Hi there,

I have attached a screenshot for the reference.

=IF(AND($AC$1="exact",AB2<=AR2, AI2>=500, AP2<=100%,AQ2<=30%), AB2*1.2, AB2*0.9)

So if this is true, ($AC$1="exact",AB2<=AR2, AI2>=500, AP2<=100%,AQ2<=30%) than I want a final bid to be 1.2% (increase by 20%) of the current bid (AB column), and this is not true than I want a final bid to be 0.90% (reduce by 10%) of the current bid (AB column). But I am not getting the result.

Can anyone please help me solve this?

TIA.
T3as3r22

 

[T3as3r22]

BE-US-bulk-operation-Sample_data.xlsx

Shared with Dropbox

www.dropbox.com

Here is the excel file.

https://dl.dropbox.com/scl/fi/z4a4uzf6as5i0pj4clbkv/BE-US-bulk-operation-Sample_data.xlsx?dl=0&rlkey=l1r1wrp9iwf6dxf1oj22fipxl

BE-US-bulk-operation-Sample_data.xlsx

Shared with Dropbox

www.dropbox.com

 

[Fluff]

Isn't that the highlighted value supposed to be higher by 20% of AB column (raw 13)

No, because AI is less than 500

 

[awoohaw]

How are you formatting column AX?

mr excel questions 27.xlsm
																												AB	AC						AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV		AX	AY
1																												Bid	Match Type						Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units	Conversion Rate	Acos	CPC	ROAS	CPA	AoV	Affordable Bids		Final Bids
2																												1.2	exact						707	1	0.14%	4.24	49.99	2	2	200.00%	8.48%	4.24	11.79	2.12	25	10		1.08
13																												0	exact						115	2	1.74%	12.93	12.99	1	1	50.00%	99.54%	6.46	1	12.9	13	1.3		0.036
T3as3r22

Cell Formulas
Range	Formula
AX2	AX2	=IF(AND($AC1="exact",$AB2<=$AR2,$AI2>=500,$AP2<=100%,$AQ2<=30%),$AB2*1.2,$AB2*0.9)
AX13	AX13	=IF(AND($AC2="exact",$AB13<=$AR13,$AI13>=500,$AP13<=100%,$AQ13<=30%),$AB13*1.2,$AB13*0.9)

 

[T3as3r22]

=IF(AND($AC$1="exact",AB13<=AR13, AI13>=500, AP13<=100%,AQ13<=30%), AB13*1.2, AB13*0.9)
This is the formula in AX13, basically I want the value in AX to be lower if any one of all value is not matching this criteria mentioned in the formula.
And that new value to be lower by 10% of AB13.

 

[T3as3r22]

Because -

AX13

AX13

=IF(AND($AC2="exact",$AB13<=$AR13,$AI13>=500,$AP13<=100%,$AQ13<=30%),$AB13*1.2,$AB13*0.9)

in this AQ13 is higher than 30%, I want AX13 to be reduced by 10% of AB13.

 

[Fluff]

I want AX13 to be reduced by 10% of AB13

But that is what is happening.

 

[awoohaw]

sorry, i did not format column AB well, here is a better version. AX13 should be correct in this, the AND resolves to FALSE.

mr excel questions 27.xlsm
																												AB	AC						AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV		AX
1																												Bid	Match Type						Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units	Conversion Rate	Acos	CPC	ROAS	CPA	AoV	Affordable Bids		Final Bids
2																												1.20	exact						707	1	0.14%	4.24	49.99	2	2	200.00%	8.48%	4.24	11.79	2.12	25	10		1.08
13																												0.04	exact						115	2	1.74%	12.93	12.99	1	1	50.00%	99.54%	6.46	1	12.9	13	1.3		0.036
T3as3r22

Cell Formulas
Range	Formula
AX2	AX2	=IF(AND($AC1="exact",$AB2<=$AR2,$AI2>=500,$AP2<=100%,$AQ2<=30%),$AB2*1.2,$AB2*0.9)
AX13	AX13	=IF(AND($AC2="exact",$AB13<=$AR13,$AI13>=500,$AP13<=100%,$AQ13<=30%),$AB13*1.2,$AB13*0.9)

 

[T3as3r22]

But that is what is happening.

And this is happening to all data. What I want is that if conversion rate is <= 100% and ACOS is <= 30% than bid to be 20% higher if not than 10% lower. How can I achieve this?

 

[T3as3r22]

sorry, i did not format column AB well, here is a better version. AX13 should be correct in this, the AND resolves to FALSE.

mr excel questions 27.xlsm
																												AB	AC						AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV		AX
1																												Bid	Match Type						Impressions	Clicks	Click-through Rate	Spend	Sales	Orders	Units	Conversion Rate	Acos	CPC	ROAS	CPA	AoV	Affordable Bids		Final Bids
2																												1.20	exact						707	1	0.14%	4.24	49.99	2	2	200.00%	8.48%	4.24	11.79	2.12	25	10		1.08
13																												0.04	exact						115	2	1.74%	12.93	12.99	1	1	50.00%	99.54%	6.46	1	12.9	13	1.3		0.036
T3as3r22

Cell Formulas
Range	Formula
AX2	AX2	=IF(AND($AC1="exact",$AB2<=$AR2,$AI2>=500,$AP2<=100%,$AQ2<=30%),$AB2*1.2,$AB2*0.9)
AX13	AX13	=IF(AND($AC2="exact",$AB13<=$AR13,$AI13>=500,$AP13<=100%,$AQ13<=30%),$AB13*1.2,$AB13*0.9)

What I want is that if conversion rate is <= 100% and ACOS is <= 30% than bid to be 20% higher if not than 10% lower. How can I achieve this?

 

[awoohaw]

is this a different question?

.36% is 10% lower of .40.