Excel Formula Help is Here
Excel formulas can be frustrating to understand, especially when you start nesting functions.
If you've used Excel for a while you've probably experienced the urge to bang your head on the desk in frustration. You may have even succumbed to that temptation. I know I have.
In the video below I share a handful of tips that will enable you to understand any formula and figure out why it’s not returning the value you’re expecting, or even worse, why it won’t let you enter it at all!
Here’s an email one of our members sent after watching this video:
“WOW! I’ve been using Excel a rather long time (mostly self-taught) and never picked up the some of the techniques you showed in this short video. I learned SIX new-to-me techniques just watching this 7-minute show! My coworkers think I’m an “Excel guru” but watching this…I’m nearer a novice!
I almost clicked out of the video when I saw it was troubleshooting formulae – a common enough task for me. But then I saw the tool tip and you moved it! I can’t tell you how many times that thing has gotten in the way, but I didn’t know it was moveable. And the other 5 troubleshooting tips in this video that I didn’t already know – wonderful, wonderful!”
| For best viewing quality: press play then 1. click the cog and select 720p HD, and 2. click the icon on the bottom right of the video to view in full screen. |  |
Relieve Frustration Around the World
Do you know anyone else who bangs their head on the desk in frustration with Excel Formulas?
Let's spare the world of Excel Formula frustration. Please click the link above to Tweet this, or scroll down to use the buttons below to share these tips with your friends and colleagues on LinkedIn, Google+1 and Facebook.
Want More?
Now you're ready to tackle anything check out my Excel Formulas List for new tricks to learn.
Sign up for our free Excel Tips and Tricks Newsletter and get loads more tips like these.
Excel CLEAN Formula
Jenni Parker
Hi
I am trying to write a formula that “copies” the month from column A named Date, to appear in column C as TEXT. if column A is EMPTY, the cell in column C remains empty. I want this to auto fill when extending the columns downwards.
I can get the formula for the month if the cell in column A has data, but if empty, it returns January. I tried auto-fill formatting only but the formula disappeared.
I need to learn what the syntax mean
Mynda Treacy
Hi Jenni,
Try this:
More on the IF Function here.
Mynda
Jenni Parker
Thanks very much for your quick response.
I need to clarify my question because that formula returned Date, whereas I need a month ie Jan-Dec from column A’s corresponding cell.
However if the cell in column A is blank, I want the Cell in column C to remain blank until Column A has the date entered. It is an ongoing spreadsheet so new rows will be added as the spreadsheet, or table is filled and extended downwards
Mynda Treacy
Hi Jenni,
If A1 contains the date 31/12/2018 then the formula will return ‘Dec’. If you’re getting a different result then there must be something wrong with the date in A1. I suspect it’s text and not a proper date serial number. More on Excel Date formats here.
The formula will also do your second requirement: “if the cell in column A is blank, I want the Cell in column C to remain blank until Column A has the date entered.”
Mynda
Christin Montoya
Question:
I am trying to calculate the average time, excluding MIN and MAX. I have tried several formulas to perform this function and end up with the same answer each time. The problem is, my answer is higher than any number in my array.
Using the formula: =(SUM(T2:T22)-MIN(T2:T22)-MAX(T2:T22))/(COUNT(T2:T22)-2)
Array: {0:30, 0:28, 0:30, 2:34, 0:01, 0:25, 0:00, 0:24, 0:25, 0:46, 0:38, 2:09, 0:24, 0:40, 0:08, 0:44, 0:36, 0:21, 0:36, 0:29, 0:21}
Answer: 10:41
I’ve confirmed this number is in hh:mm format. I don’t understand what’s wrong, can you help?
Mynda Treacy
Hi Christin,
I get 0:33 with your formula. Are you able to share your Excel file on our forum where we can troubleshoot further?
Mynda
Florence
purchases price (PP) $530k
valuation price (VP) $500k
loan available 80% (L)
how to create formula if PP more than VP, then L=80%*PP, if not L=80%VP.
Thank you
Catalin Bombea
Hi Florence,
Try this:
=0.8*MAX(PP,VP)
Catalin
ASHOK JAJAL
In the below example my problem is with brokerage figure which is based on 0.01% of turnover, it should not be cross the 40 . even if i will increase buying price, selling price, or quantity , insort if turn over figure come 450000 so brokerage figure should not reach 45 , but below 40 it should be show exact figure ie.39,38,35,31,19,15,9 whatever , i m looking for its excel formula
Particular Buy Price Sell Price Qty.
—————————– ————- ———— ——
100 100 1000
Turnover 200,000
Brokerage 0.01% of turnover 20
Stt Total 25
Total Tran Charge 7.8
Service Tax 5.74
Education Cess 0.11
Higher Edu Cess 0.06
SEBI Charges 0.20
Total Tax and charges 78.91
Points to Breakeven 0.08
Catalin Bombea
Hi Ashok,
Can you please send us a file with your data and with complete details on what you are trying to achieve? It’s very hard to work on data provided, we will gladly help you if we have clear information.
You can use the Help Desk: https://www.myonlinetraininghub.com/helpdesk/
Thank you,
Catalin
rosa
what is wrong with this formula? I am not getting the expected results. Is there something wrong with my Date syntax?
=COUNTIFS(D2:D7,”complete”,Z2:Z7,”US”,AA2:AA7, “UD”,AD2:AD7, Date <=DATEVALUE("1/31/2013"))
Thanks in advance for your help 🙂
Mynda Treacy
Hi Rosa,
Is ‘Date’ used here: ‘Date <=DATEVALUE(“1/31/2013”)’ a named range? If so you don’t need it in the criteria. Your criteria is the date inside the parentheses. So your formula probably should be more like this:
=COUNTIFS(D2:D7,"complete",Z2:Z7,"US",AA2:AA7, "UD",AD2:AD7, <=DATEVALUE("1/31/2013"))If that doesn’t work please send me your workbook via the help desk so I can see what is in the other cells being referenced by the formula.
Kind regards,
Mynda.