AI Module 03

My excel data has a column called Material Cost in column G, with data starting in cell G2. Write a formula I can paste into cell J2 that will return Review if the value in column G is greater than 250, or OK if less than 250.

I have the following formula in Excel. Why is it returning inconsistent or wrong answers? =SUMIF(E2:E13, "Open", F:F)

I need to know how may work orders have a status of Open. The work orders are in rows and the status is in cell E2:E13. I need a separate formula to calculate the total of the MaterialCost column G2:G13 where the Neighborhood in I2:I13 is equal to Westside.

Create an Excel sign-in sheet for a community wellness workshop hosted by the City of Fort Lauderdale Parks and Recreation Department. Include columns for: attendee first name, last name, zip code, phone number, email address, whether they heard about the event through social media/flyer/word of mouth/city website, and a signature column. Add a header at the top with the event name, date, location, and department name. Format it so it is clean and easy to read when printed. Ensure there are large, fill-in-the-blank lines beneath each column with enough vertical space for attendees to comfortably handwrite their information when printed.

Create an Excel departmental budget tracker for a county government parks department. Include columns for: budget category, annual budget amount, Q1 actual spending, Q2 actual spending, Q3 actual spending, Q4 actual spending, total actual spending year to date, remaining budget, and percentage used. Use formulas for all calculated columns. Add a bar chart comparing budgeted vs. actual spending by category. Use conditional formatting to highlight any category where spending has exceeded 90% of the budget in orange, and any category that has exceeded the budget in red. Include eight realistic budget categories with sample data

I have an Excel worksheet called Logical Functions that contains two named tables.
The first table is named Salary_Info and spans A1:I51. It has the following columns: First Name, Last Name, County, Email, Department, Salary, Salary + Adjustment, Tax Withheld, and 401K.
The second table is named County_COLA and spans K6:L11. It has two columns: County and Adjustment.
Please write a formula for the Salary_Info[Salary + Adjustment] column that does the following:

Looks up the value in Salary_Info[County] and finds an exact match in County_COLA[County]
Adds Salary_Info[Salary] to the matching County_COLA[Adjustment] value
If no match is found, displays the text Calc Error

Please provide the formula only, with a brief explanation of how it works.

I need a formula for Salary_Info[Tax Withheld] that will compare Salary_Info[Salary + Adjustment] against Tax_Withholding[Low Range] and Tax_Withholding[High Range] to find which bracket it falls between. When found, multiply Salary_Info[Salary + Adjustment] by Tax_Withholding[Percent Withheld]. If no match, return 'Calc Error'. The formula will use XLOOKUP with a Boolean array match, so please account for the fact that multiplying Boolean arrays in Excel converts TRUE/FALSE to 1/0 and write the formula accordingly.

I have the formula "=IF(ISERROR(INDEX($D$2:$D$9,MATCH(1,(($C$2:$C$9="East")*($E$2:$E$9="Open")),0))),0,SUMPRODUCT(($C$2:$C$9="East")*($E$2:$E$9="Open")*($D$2:$D$9))/IF(SUMPRODUCT(($C$2:$C$9="East")*($E$2:$E$9="Open"))=0,1,SUMPRODUCT(($C$2:$C$9="East")*($E$2:$E$9="Open"))))" in cell G2 referring to the dataset in the screenshot. Can you explain in straightforward terminology what this formula does?

Can you write a simplified version of the formula "=IF(ISERROR(INDEX($D$2:$D$9,MATCH(1,(($C$2:$C$9="East")*($E$2:$E$9="Open")),0))),0,SUMPRODUCT(($C$2:$C$9="East")*($E$2:$E$9="Open")*($D$2:$D$9))/IF(SUMPRODUCT(($C$2:$C$9="East")*($E$2:$E$9="Open"))=0,1,SUMPRODUCT(($C$2:$C$9="East")*($E$2:$E$9="Open"))))" that will not break and handles errors better?

Analyze the data in the Salary Info table and draw any results you can.

Using the data in the Subscriptions_Table, build a pivot table that analyzes expenses by publication by month and year. If there is not expense for a publication in a given month, enter 0. Put the publications in rows and the months in columns.