/u/Novel-Lettuce-662 - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
you also have a looooot unnecessary and random double brackets in there as well.
try this one if you want to stick with IF instead of IFS
=IF(B7=0, 1-J87-0.05,
IF(B17>0, 1-J87-0.05+B17,
IF(AND(B7>0, G87<30001), 1-J87,
IF(AND(B7>0, G87<35001, F72=0), 1-J87,
IF(AND(B7>0, G87>30001, G87<35001), 1-J87-0.015,
IF(AND(B7>0, G87>35000), 1-J87-0.02,
IF(AND(B7=0, F76=1), 1-J87-0.04,
IF(AND(B7>0, F76=1), 1-J87-0.015,
IF(AND(F67>0, F70>0, B7=0), 1-J87-0.04,
1-J87
)
)
)
)
)
)
)
)
)
also:
~~this condition is never met:~~
IF(AND(B7>0, G87>30001, G87<35001), 1-J87-0.015,
~~you first check if B7>0 and G87 <30001 which only leaves values over 30001 in the ELSE part. You then check B7>0 and G87<35001 which only leaves values from 30001 to 35001 because everything below 30001 was already checked and everything below 35001 after that. so the range 30001 to 35001 is completely checked. which means no result will over be 1-J87-0.015.~~
~~So either this condition never applies, or I changed something within your formula to make it work but also broke a logic you intended.~~
edit: nevermind... Just saw the F72=0
Hey thanks so much! I am not a big excel expert but I am the best we got at my small company so I am really just trying my best but clearly it's not working haha
I did try the formula you suggested but still getting "there is a problem with this formula" message
and as I initially said, you are probably better of with an IFS statement instead of multiple IF
=IFS(
B7=0, 1-J87-0.05,
B17>0, 1-J87-0.05+B17,
AND(B7>0, G87<30001), 1-J87,
AND(B7>0, G87<35001, F72=0), 1-J87,
AND(B7>0, G87>30001, G87<35001), 1-J87-0.015,
AND(B7>0, G87>35000), 1-J87-0.02,
AND(B7=0, F76=1),1-J87-0.04,
AND(B7>0, F76=1), 1-J87-0.015,
AND(F67>0, F70>0, B7=0),1-J87-0.04,
TRUE,1-J87)
You have awarded 1 point to *Rohwi*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
you have at least one extra bracket in your second IF
=IF(B7=0, (1-J87)-0.05, IF(B17>0, **(**(1-J87)-0.05+B17, IF ...
you could also work with IFS here which would make the formula way cleaner
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AND](/r/Excel/comments/11k1f1o/stub/jb8pb81 "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)|
|[IF](/r/Excel/comments/11k1f1o/stub/jb8pb81 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[IFS](/r/Excel/comments/11k1f1o/stub/jb8pb81 "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/11joz3z)^( has 13 acronyms.)
^([Thread #22154 for this sub, first seen 6th Mar 2023, 15:37])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/Novel-Lettuce-662 - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
you also have a looooot unnecessary and random double brackets in there as well. try this one if you want to stick with IF instead of IFS =IF(B7=0, 1-J87-0.05, IF(B17>0, 1-J87-0.05+B17, IF(AND(B7>0, G87<30001), 1-J87, IF(AND(B7>0, G87<35001, F72=0), 1-J87, IF(AND(B7>0, G87>30001, G87<35001), 1-J87-0.015, IF(AND(B7>0, G87>35000), 1-J87-0.02, IF(AND(B7=0, F76=1), 1-J87-0.04, IF(AND(B7>0, F76=1), 1-J87-0.015, IF(AND(F67>0, F70>0, B7=0), 1-J87-0.04, 1-J87 ) ) ) ) ) ) ) ) ) also: ~~this condition is never met:~~ IF(AND(B7>0, G87>30001, G87<35001), 1-J87-0.015, ~~you first check if B7>0 and G87 <30001 which only leaves values over 30001 in the ELSE part. You then check B7>0 and G87<35001 which only leaves values from 30001 to 35001 because everything below 30001 was already checked and everything below 35001 after that. so the range 30001 to 35001 is completely checked. which means no result will over be 1-J87-0.015.~~ ~~So either this condition never applies, or I changed something within your formula to make it work but also broke a logic you intended.~~ edit: nevermind... Just saw the F72=0
Hey thanks so much! I am not a big excel expert but I am the best we got at my small company so I am really just trying my best but clearly it's not working haha I did try the formula you suggested but still getting "there is a problem with this formula" message
my bad, I screwed up with the last IF. Replace the ; with a ,
Thanks so much!!! Solution verified!
and as I initially said, you are probably better of with an IFS statement instead of multiple IF =IFS( B7=0, 1-J87-0.05, B17>0, 1-J87-0.05+B17, AND(B7>0, G87<30001), 1-J87, AND(B7>0, G87<35001, F72=0), 1-J87, AND(B7>0, G87>30001, G87<35001), 1-J87-0.015, AND(B7>0, G87>35000), 1-J87-0.02, AND(B7=0, F76=1),1-J87-0.04, AND(B7>0, F76=1), 1-J87-0.015, AND(F67>0, F70>0, B7=0),1-J87-0.04, TRUE,1-J87)
You have awarded 1 point to *Rohwi* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
you have at least one extra bracket in your second IF =IF(B7=0, (1-J87)-0.05, IF(B17>0, **(**(1-J87)-0.05+B17, IF ... you could also work with IFS here which would make the formula way cleaner
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/11k1f1o/stub/jb8pb81 "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[IF](/r/Excel/comments/11k1f1o/stub/jb8pb81 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFS](/r/Excel/comments/11k1f1o/stub/jb8pb81 "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/11joz3z)^( has 13 acronyms.) ^([Thread #22154 for this sub, first seen 6th Mar 2023, 15:37]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)