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

 

[awoohaw]

what is the error message you are getting? Have you tested to make sure the statement in the AND all resolve to true?
HIghlight the AND() and press F9, does it resolve to TRUE when you think it should?
If not highlight each component of the AND and examine why one or more of them resolve to FALSE.

 

[Fluff]

Which ones are wrong? We cannot see what is in AC1 or col AI

 

[T3as3r22]

Oh sorry, I did hide some columns by mistake, here is the updated screenshot.

 

[awoohaw]

we need to know the error you're getting?
And although iimages are helpful the forum needs to recreate your scenario manually, the xl2bb add in (link below) is very useful in situations such as yours.
You may have extra spaces in cells, a date may be text instead of a datevalue, or you could just have a comma in the wrong place or pointing to a wrong cell.
Help the forum help you.

But at a minimum... give the formula you are having a problem with. The cell you have it in, and what is your expected result. It looks like you have the formula in AX2, what is your expected result.

 

[Fluff]

here is the updated screenshot.

You haven't told us which ones are wrong & why.

 

[T3as3r22]

I want to achieve this.

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.

I am unable to install xl2bb, Mini Sheet and Table only options disabled in my Excel. I am using license version of Excel 2021.

 

[T3as3r22]

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

 

[awoohaw]

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

are you saying cell AX13 is not supposed to be a result of .04? what is the value you want to see?

 

[T3as3r22]

Here is the excel file.

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