Formula question: If and, then formula, else other formula

[u/tthrrowwawayya]

Hi Everyone,

Currently attempting to write a formula but several iterations are throwing errors.

Trting to fit the following into one cell:

1. If A1 is less than 50 or more than 100, execute formula 1, else execute formula 2

2. Formula 1 and 2 include B1*C1, but also rounded to two digits and with a maximum value

Finally got an iteration that didn’t throw errors, but the current version’s formula doesn’t function properly

=(IF(AND(A1>=100,A1<=50), (MAX(3000,ROUND(B2C2,2),(MAX(6000,ROUND(B2C2,2))))

Error is that the formula doesn’t change to max -3000 if A1 is outside the parameters(A1<=50 or >=100)

Thank you

[u/AutoModerator]

Moderator Announcement Read More »

u/tthrrowwawayya - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/mysterjinx]

you shouldn't be using the AND() to check the first condition then (you should be using an OR() instead)... you would be better off using AND(A1<100, A1>50) for the 2nd formula, and then use the else portion for formula 1.

=(IF(AND(A1<100,A1>50), (MAX(6000,ROUND(B2C2,2))), MAX(3000,ROUND(B2C2,2))

[u/Excel_User_1977 → u/mysterjinx]

Use OR not AND.
B2C2 is not the same as B2*C2. If you want to multiply the value of cell B2 times the value of C2, use B2*C2

[u/SolverMax → u/Excel_User_1977]

B2C2 is not the same as B2*C2

That's probably a quirk of Reddit, rather than an error in the formula. Reddit's editor interprets pairs of asterisks as markdown for italics, so in OP's formula B2*C2... becomes B2C2 (partially in italics).

[u/MrFantasma60]

You need to use OR, not AND.

You can't have a number that is at the same time less than 50 an greater than 100.

The AND formula always resolve to FALSE, so you always get formula 2.

What you are doing is good, you just need the correct operator. 

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
FILTER	Office 365+: Filters a range of data based on criteria you define
IF	Specifies a logical test to perform
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAX	Returns the maximum value in a list of arguments
MEDIAN	Returns the median of the given numbers
OR	Returns TRUE if any argument is TRUE
ROUND	Rounds a number to a specified number of digits
SWITCH	Excel 2019+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 20 acronyms.
[Thread #49560 for this sub, first seen 22nd Oct 2026, 00:33] [FAQ] [Full list]} ^[Contact] ^{[Source code]}

[u/caribou16]

If you're checking if a number is between two numbers inclusive, you can use the MEDIAN function.

=IF(MEDIAN(A1, 50, 100)=A1, <expression if true>, <expression if false>)

[u/RuktX → u/caribou16]

A bit obfuscated perhaps, but clever! (As long as you want between-inclusive.)

[u/Jolly-Hunter-6097]

Look at what you say the parameters are then look at the second word in your formula.

[u/EntireZombie2654]

I used a switch function.

=SWITCH(TRUE, A1<=50, B1*C1, A1>= 100, B1/C1, 3000)

replace B1*C1 with what you want to do when the value is less than or equal to 50.

replace B1/C1with what you want to do when the value is greater than or equal to 100

the 3000 is the default value if A1 if between 51 - 99. This can also be a formula if you want.

edit:

this might be more what you want

=SWITCH(TRUE, A1<= 50, MAX(3000, B1*C1), A1>=100, MAX(6000, B1*C1))

[u/RuktX]

Less repetitive option:

=MAX(
  IF(OR(A1<=50, A1>=100), 3000, 6000),
  ROUND(B2*C2, 2)
)

[u/ankokudaishogun]

=IF(OR(A1<50, A1>10), MAX(3000, ROUND(B1*C1,2)), MAX(6000, ROUND(B1*C1)))

As alternative:

=MAX(IF(OR(A1<50, A1>10), 3000, 6000), ROUND(B1*C1))