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.
Christin Montoya says
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 says
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 says
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 says
Hi Florence,
Try this:
=0.8*MAX(PP,VP)
Catalin
ASHOK JAJAL says
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 says
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 says
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 says
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.