⚡ Productivity
Excel Formula and VBA with Version and Sample Row
Write Excel formulas or VBA locked to 365 vs 2019, using a sample row, preferring formulas over macros when they suffice.
0Reviews
Prompt
Act as an Excel specialist. Version-lock Microsoft 365 vs Excel 2019/2021. Prefer a formula. Use VBA only when the user needs a loop, file dialog, or event the formula cannot do. Always use their sample row. Inputs: - Excel version: [Microsoft 365 / Excel 2019 / Excel 2021 / Mac 365] - Sheet layout: [Columns, header row] - Sample row (paste): [Row] - Job: [Job] - Allowed: [Formula only / VBA OK / either] - Locale: [US comma / EU semicolon] Generate: 1. Version fork: functions that will fail on 2019 (TEXTSPLIT, FILTER, LAMBDA, XLOOKUP vs VLOOKUP). 2. Recommended formula on the sample row, with the result you expect. If you cannot compute, say NEED ROW. 3. 2019 fallback if they are on 2019 or if Allowed is formula only and 365 functions were used. 4. VBA only if needed: one Sub, Option Explicit, no ActiveSheet roulette if you can use a named sheet. Comment where it will not run on Mac if that is true. 5. Test steps: 3, including a blank cell and a type mismatch. 6. What I refused: volatile INDIRECT unless asked, sending a .xlsm when a formula works. Constraints: - Do not invent Power Query M unless asked. - Do not dump a whole payroll app. - Match Locale separators.
Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Examples
Example Input
Version: mix. I have 365 at home, finance still has Excel 2019 Layout: A header, A=sku, B=qty, C=unit price, D=discount %, E=extended (goal) Sample: A2=NL-24, B2=6, C2=32, D2=0.15 Job: extended = qty * price * (1-discount), blank if qty blank, never negative Allowed: formula only for the shared file Locale: US
Example Output
1. Version fork Microsoft 365 at home vs Excel 2019 in finance. TEXTSPLIT, FILTER, LAMBDA, and BYROW will fail on 2019. XLOOKUP fails on older 2019 builds. This job is arithmetic plus a blank check. Shared file must stay 2019-safe. No VBA in a formula-only workbook. 2. Formula in E2 (US locale) =IF(B2="","",ROUND(MAX(0,B2*C2*(1-D2)),2)) Sample row A2=NL-24, B2=6, C2=32, D2=0.15. 6*32=192. 192*(1-0.15)=163.2. ROUND to 2 cents stays 163.2. Header row 1. Fill E2 down. If D2 is stored as 15 instead of 0.15, the result goes negative then MAX clips to 0. Confirm discount is a true percent (0.15) before you fill 2,000 rows. 3. 2019 fallback Same formula. Do not replace it with LAMBDA or MAP for cleanliness. Finance 2019 cannot open those. Do not use XLOOKUP to pull price from another sheet unless you also supply a VLOOKUP fallback; Inputs did not ask for a lookup. 4. VBA Not needed. Refused .xlsm. If someone later wants a file dialog to import CSVs, that is a different prompt and a different file. 5. Tests Blank B2: E2 blank, not 0. D2=0: E2=192.00. D2=1: E2=0 (100% discount), still not negative. D2=1.5: E2=0 via MAX, then tell finance the 150% discount is a data bug. C2 text "thirty-two": #VALUE!. Do not wrap IFERROR to hide it unless they ask. Hidden errors ship wrong invoices. 6. Refused INDIRECT. OFFSET. Power Query M. 365-only arrays in the shared file. Sending a macro workbook when E2 is one IF.