Stumped on combining AND/IF/OR formula
I need a formula where if Innovate Audit is Yes, or Innovate Audit Upgrade is Yes, but Pull Print is No, then the the # of devices requiring in printer agent/40 +1
Here is what I came up with so far...
=IF(OR([Innovate Audit]@row = "Yes", [Innovate Audit Included in Upgrade?]@row = "Yes"), IF(AND([HP Advance Pull Print?]@row = "No"), ([Devices Requiring In- Printer Agent]@row/40+1) ,0)
Best Answer
-
Smartsheet's information on #UNPARSEABLE suggests we " Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ")."
May I suggest selecting each of the square bracket references and replacing the reference by clicking in the column that relates to the data. This will ensure the correct column is referenced.
In the pic provided I'm not seeing any of the column names in the formula (e.g. [HP Advance Pull Print?]). Could it be that the column name doesn't have a question mark, or has a space after it?
You'll know when it's referencing data when each square bracket reference changes colour, like this:
Hope this helps?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Answers
-
Hi Stacy,
It looks like your formula works (refer screenshot below).
I normally combine these as an IF(AND(... , OR(... , ...)), 1 , 0) formula (also below, which also works)
=IF(AND([HP Advance Pull Print?]@row = "No", OR([Innovate Audit]@row = "Yes", [Innovate Audit Included in Upgrade?]@row = "Yes")), ([Devices Requiring In- Printer Agent]@row / 40 + 1), 0)
Hope this helps?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
When I use my formula and yours, i get #UNPARSEABLE :(
-
Smartsheet's information on #UNPARSEABLE suggests we " Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ")."
May I suggest selecting each of the square bracket references and replacing the reference by clicking in the column that relates to the data. This will ensure the correct column is referenced.
In the pic provided I'm not seeing any of the column names in the formula (e.g. [HP Advance Pull Print?]). Could it be that the column name doesn't have a question mark, or has a space after it?
You'll know when it's referencing data when each square bracket reference changes colour, like this:
Hope this helps?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
For some reason I couldn't get the cells to highlight on the row I was working on the formula with. I went down a row, added the formula and then made it a column formula and that fixed my issue and the formula now is working great! Thanks for all your help!
-
Great news.
Glad to be of help.
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!