I just figured out my question below...all by myself :) What I needed was: =IF(NOT(S12"Y"), Q9+T9+3.8+5.64, Q9+T9+3.8) That's probably very simple for all you Excel experts. So pleased I got there in the end.
Ralph, your channel is awesome, i have just subscribed to it. I noticed you use Microsoft Excel. I use Excel for Mac, and I am struggling with some VBA codings... Do you have any channel in mind that could suggest me to follow in order to learn VBA from someone who uses Mac? Kind regards, Lucas.
hii Ralph Phillips, can you teach me how to solve this excel prob ? i got a list of thousands of file names.. they all named in numeric file name. for example, 001-a.ppt 001-b.mp3 001-c.mp4 and 002-a.mp3 002-b.mp4 002-c.ppt and 003-a.ppt 003-b.mp3 and so on..... i have exported the text file of my folder file list into excel and separated the numerical part of file name into a column of cell - A1 to A...... i need to find out if there is any missing sequence of the file list.. ya, i know it could be difficult, because my file aren't perfectly in order.. some got 2 and other got 3 files of a numeric number. do you have any way to make it to listed out which file missing ? i need it to tell me if i have got a missing mp3 or ppt of a specific sequence of file.... pls teach me if it is possible. thanks andrew
I have a workbook of expenses with a worksheet for each month. In each month when an expense is entered, a category (From a Schedule C, an IRS tax form) is also entered. I've created a 13th worksheet and am trying to come up with a formula that will scan every line for a category, say "Office Supplies" in say cell C5, then take the amount in Cell H5 and add it to the totals on my 13th worksheet. I've been doing it manually and believe there has to be a way, so each category is totaled and added to the final total worksheet. As I can break it down into steps, would it be possible to accomplish this with a macro? Thanks
How would I make a statement if I needed to show between a range of numbers in the same cell? example: 90-100=Outstanding, 80-89=Meets expectation, 70-79=Some Expectations and so on.
I'm trying to workout a formula to exercise one of two sums dependent on a value. I have a Y/N cell to include an item's weight. If I choose "Y" it should perform a SUM including that item, else it should output the result without the item. Therefore the SUM would be L29+T9+3.8 and then +5.64 or -5.64 dependent on my selection of "Y" or "N" in another cell. I'm new to excel. Not having any success on this final step.
Sir, I wrote all the data of your first example into my worksheet, but I was NOT getting the correct results in Bonus Column. Could you please clarify.
nice one. u should record another one with scenario when you have more variants and show how to move from if() to vlookup() function... but still, nice exercice :-)
How to do the second example if you have one number to control but 4 possible outcomes like "Yes", "No", "Maybe", "Go away". The number also has two decimals. Is it even possible? I have tried everything i can...
Do you know how I would use an if function to return only ONE value? My formula is =if(C3=0,AA&AB&AK,C3). The problem is that my true statement sometimes has values in all 3 but I only need one to be returned. I fix it by filtering column C and deleting any duplicate values in AA, AB and AK, but it would be nice to figure out how to return only ONE value and not have to filter and delete. Any ideas?
Hello Ralph, thank you for all these valuable videos. Please help how to auto number on worksheet 2 pickup the last number from worksheet 1? Thank you.
I need to V Lookup the data from coloum 0 to 9 if it results to N/A, then need to check from 0 to 10...can i please know the formula to be used on the conditions.. Reply me pls
hi. need ur help. what if i have multiple criteria for places. e.g. If (B11=Feedback and A11= UK then C11= India), if (B11=Scanning and A11=UK then C11=UK)
I want to write an IF statement to group come vaules but unsure how to go about it. Want to group < or = 0 to be group 1, 1-10 group 2, 11-20 group 3 etc. Can anyone help?
Ralph, need your assistance. I have 5 cells in a row(B1:F1) with numbers. I would like for format each cell(bold Red) base on the value of A1 +/- .010. So, if the value in each cells is outside +/- .010 of Cell A1, bold and Red the value in the cell.
Am justice from ghana. Got introduced to excel and am enjoying its study. Want to get prepared for the specialist exam but do not know how to go about the registration for the exam. I want help kpokuj@gmail.com.
Thanks for posting the video. I understood the theory of what you were saying but when it came to writing the formula i couldnt get it to work (never mind). If anyone can see the mistake in the formula below and is able to help with the answer i would be very greatful =IF(or(B41=$A$40,"B41-$A$40/100*9")) Basically if Cell B41 is less than Cell A40 then £0.00 should appear and it does, however the second part of the formula doesn't work i.e. if B41 is greater than or equal to A40 then take B41 and minus A40 then times by 9%
I love this video but I am struggling because I'm having to use google sheets for a client. I am a self taught excel user and this is a new one for me. Here's my issue... Current formulas show Cell A1 - contact Date Cell B1=DATEVALUE(A1)+2 Cell C1 is a Data Validation with a drop down option for NR (No Response) This is to be used if the person hasn't responded to the last two messages sent out. What I want is for D1 to conclude the following... IF C1 is NR and A1=DATEVALUE(A1)>+14 THEN C1="FOLLOW UP" NOTHING I try is working and so I have logically reached the end of my self taught abilities. :( Any help would be greatly appreciated.
NEED HELP, I AM USING EXCEL AND I HAVE AN ADDING PROBLEM .... I HAVE G5 (0.31) AND I5 (2.98) ADDING UP IN J5 (3.28) ... BUT 0.31+2.98 IS 3.29 , I AM OFF BY 1 CENT , WHY ? 99 % OF MY LINES AND COLUMS ADDS CORRECTLY , BUT EVERY OTHE 4TH - 5 TH ONE IS OFF BY 1 CENT.
Hi, If you help me, you will save me many hours of work. I need a formula which says that if cell A5 equals cell B5, then cell E5 must be copied from cell C5. Can such a formula be developed in excel? Many many thanks beforehand
ZestyDirectorHD Yes, but you won't use the currency symbol or the comma in the function. A currency value is just a number, regardless if it's US dollars or British Pounds.
use a logical function to find out if each customer is eligible for a daily deal (Y) or not (N), based on. (*Hint - use a nested if function) Monday 20% off total bill Tuesday Free dessert Wednesday Mains - Buy 1 Get 1 Thursday 10% off total bill Friday No deal Saturday No deal Sunday Free side order please help on this problem
In the screen shot how can i tell excel to leave the cells "#DIV/0!" blank whenever #DIV/0! shows up? These cell are linked to another worksheet which are formulas that i dont want seen if there is no data entered
Hi, your tutorial is good, but still not able to figure out how to apply it in my excel.i am creating a salary calculator. I need three logic. Logic A = if advance is between 2000-5000 then flat 1500 should be deducted from salary Logic B = if advance is < 2000 then flat 500 should be deducted from salary. Logic C = if advance is >5000 then flat 3000 should be deducted from salary How to go about it. Thanks
Every person gets a bonus in that example but there are two cases 1- If a person works atleast 9 years AND has a certification --> Multiply years worked by 100 2- if they did not work 9 years or if they dont have certification --> Multiply years worked by 50 person 8 fell into the second category
What i like in particular is the simplicity and clarity
I just figured out my question below...all by myself :)
What I needed was: =IF(NOT(S12"Y"), Q9+T9+3.8+5.64, Q9+T9+3.8)
That's probably very simple for all you Excel experts. So pleased I got there in the end.
Huge help with this straight forward, easy to follow tutorial - many thanks for keeping it simple Ralph...
Really simple and easy method to teach excel formulas, well done
Thank u sir...very helpful video...tomorrow is my computer practical xam...helped me a lot
Very amazing tutorial,have be struggling to understand the use of OR and AND but not have got everything right... thumbs up
Keep revisiting this so must be a good sign
Simplified and Straightforward
THANK YOU SO MUCH!!! Some heroes don't wear capes!
Awesome, He's very thorough in explaining each step of the process, I greatly appreciate that. Very Helpful
Really helpful. I like your thinking, really simplifies it.
Beautifully explained!
In 8 minutes you explained what my teacher couldn't in 2 hours.
Thank you! You're a rockstar! This solved my vlookup with multi criteria challenge .
Ralph, your videos are great! Thank you!
Ralph, thank you so much, you doing a marvelous job.
Thanks a lot. The lesson is very easily understandable.
Wow. Great in explaining it. I used to have problem doing it. Thanks
Easy to understand tutorial, thanks!
Dude, you missed your calling, great teacher and instructor.
Thanks so much for this tutuorial! It was hugely helpful to me!
Very important lesson and well explained, Thank you!
Hi can I get the original question somewhere so that I can practice this? Thank you for a great tutorial
helped me so much❤️❤️❤️
Ralph, your channel is awesome, i have just subscribed to it. I noticed you use Microsoft Excel. I use Excel for Mac, and I am struggling with some VBA codings... Do you have any channel in mind that could suggest me to follow in order to learn VBA from someone who uses Mac? Kind regards, Lucas.
excellent
love your tutorials !!!!
I was wondering why no one double clicks to auto fill? I've watch several tutorials and everyone click and drag.
Helpful video Sir... Thank u 🙏🙏
hii Ralph Phillips,
can you teach me how to solve this excel prob ?
i got a list of thousands of file names.. they all named in numeric file name.
for example,
001-a.ppt
001-b.mp3
001-c.mp4
and
002-a.mp3
002-b.mp4
002-c.ppt
and
003-a.ppt
003-b.mp3
and so on.....
i have exported the text file of my folder file list into excel and separated the numerical part of file name into a column of cell - A1 to A......
i need to find out if there is any missing sequence of the file list..
ya, i know it could be difficult, because my file aren't perfectly in order.. some got 2 and other got 3 files of a numeric number.
do you have any way to make it to listed out which file missing ?
i need it to tell me if i have got a missing mp3 or ppt of a specific sequence of file....
pls teach me if it is possible.
thanks
andrew
I have a workbook of expenses with a worksheet for each month. In each month when an expense is entered, a category (From a Schedule C, an IRS tax form) is also entered. I've created a 13th worksheet and am trying to come up with a formula that will scan every line for a category, say "Office Supplies" in say cell C5, then take the amount in Cell H5 and add it to the totals on my 13th worksheet.
I've been doing it manually and believe there has to be a way, so each category is totaled and added to the final total worksheet. As I can break it down into steps, would it be possible to accomplish this with a macro?
Thanks
Nice explanation
Thanks
Very helpful. Nice. Thanks for the tutorials!
How would I make a statement if I needed to show between a range of numbers in the same cell?
example: 90-100=Outstanding, 80-89=Meets expectation, 70-79=Some Expectations and so on.
Thank you so much.....it works👍
so the first problem was an if(and(or...)) function and the second problem was an if(or(and...)) function?
I'm trying to workout a formula to exercise one of two sums dependent on a value.
I have a Y/N cell to include an item's weight. If I choose "Y" it should perform a SUM including that item, else it should output the result without the item. Therefore the SUM would be L29+T9+3.8 and then +5.64 or -5.64 dependent on my selection of "Y" or "N" in another cell.
I'm new to excel. Not having any success on this final step.
Sir, I wrote all the data of your first example into my worksheet,but I was getting the correct results in Bonus Column. Could you please clarify.
THANKS for great help for double logic ...
Sir, I wrote all the data of your first example into my worksheet, but I was NOT getting the correct results in Bonus Column. Could you please clarify.
nice one. u should record another one with scenario when you have more variants and show how to move from if() to vlookup() function...
but still, nice exercice :-)
Thank you, very easy to follow. You helped me figure out my work stuff.
How to do the second example if you have one number to control but 4 possible outcomes like "Yes", "No", "Maybe", "Go away". The number also has two decimals. Is it even possible? I have tried everything i can...
Do you know how I would use an if function to return only ONE value? My formula is =if(C3=0,AA&AB&AK,C3). The problem is that my true statement sometimes has values in all 3 but I only need one to be returned. I fix it by filtering column C and deleting any duplicate values in AA, AB and AK, but it would be nice to figure out how to return only ONE value and not have to filter and delete. Any ideas?
simple and clear
Hello Ralph, thank you for all these valuable videos.
Please help how to auto number on worksheet 2 pickup the last number from worksheet 1? Thank you.
man this Video was soo helpful thankx bro u did a great job out there
I need to V Lookup the data from coloum 0 to 9 if it results to N/A, then need to check from 0 to 10...can i please know the formula to be used on the conditions.. Reply me pls
pls if i have number between two value like 0.55
hi. need ur help. what if i have multiple criteria for places. e.g. If (B11=Feedback and A11= UK then C11= India), if (B11=Scanning and A11=UK then C11=UK)
need to leave a cell blank if another cell is populated...but i need a formula in the cell that cell. Seems like I'm close with the if, and, or.
I want to write an IF statement to group come vaules but unsure how to go about it.
Want to group < or = 0 to be group 1, 1-10 group 2, 11-20 group 3 etc. Can anyone help?
very clear - thank you
Can you help me to resolve some issue with "IF" function please
So helpful! Thank you
When and where i can use and or or logical_test?
Great work
Ralph, need your assistance. I have 5 cells in a row(B1:F1) with numbers. I would like for format each cell(bold Red) base on the value of A1 +/- .010. So, if the value in each cells is outside +/- .010 of Cell A1, bold and Red the value in the cell.
Good video
Simple and clear!
Am justice from ghana. Got introduced to excel and am enjoying its study. Want to get prepared for the specialist exam but do not know how to go about the registration for the exam. I want help kpokuj@gmail.com.
Well explained
Thanks for posting the video. I understood the theory of what you were saying but when it came to writing the formula i couldnt get it to work (never mind).
If anyone can see the mistake in the formula below and is able to help with the answer i would be very greatful
=IF(or(B41=$A$40,"B41-$A$40/100*9"))
Basically if Cell B41 is less than Cell A40 then £0.00 should appear and it does, however the second part of the formula doesn't work i.e. if B41 is greater than or equal to A40 then take B41 and minus A40 then times by 9%
I love this video but I am struggling because I'm having to use google sheets for a client. I am a self taught excel user and this is a new one for me. Here's my issue...
Current formulas show
Cell A1 - contact Date
Cell B1=DATEVALUE(A1)+2
Cell C1 is a Data Validation with a drop down option for NR (No Response) This is to be used if the person hasn't responded to the last two messages sent out.
What I want is for D1 to conclude the following...
IF C1 is NR and A1=DATEVALUE(A1)>+14 THEN C1="FOLLOW UP"
NOTHING I try is working and so I have logically reached the end of my self taught abilities. :(
Any help would be greatly appreciated.
NEED HELP, I AM USING EXCEL AND I HAVE AN ADDING PROBLEM .... I HAVE G5 (0.31) AND I5 (2.98) ADDING UP IN J5 (3.28) ... BUT 0.31+2.98 IS 3.29 , I AM OFF BY 1 CENT , WHY ? 99 % OF MY LINES AND COLUMS ADDS CORRECTLY , BUT EVERY OTHE 4TH - 5 TH ONE IS OFF BY 1 CENT.
Hi, how can i download excel data
Nicceee... Thank you. 😊
Can a CountA function be used in an IF function? Thanks
I figured it out, thanks, it's CountIF
can any one help to formulate an equuation using if
Hi sir m a big fan of you
I just have a query how to color a cell red while getting low point and green while getting high points with formula
Hi, If you help me, you will save me many hours of work. I need a formula which says that if cell A5 equals cell B5, then cell E5 must be copied from cell C5. Can such a formula be developed in excel? Many many thanks beforehand
Please any one can help me what formula I use for in excel. cell c I want figure from a or b. If cell b = ***** auto take Figure from cell b
is it possible have or and and in one if function
Yep
Done
very helpful
Thank you!
Fantastic...
Thanks a lot
would you be able do it with pounds because i want to do If(or(C3>=£ 12,000 etc
ZestyDirectorHD Yes, but you won't use the currency symbol or the comma in the function. A currency value is just a number, regardless if it's US dollars or British Pounds.
ok thx
use a logical function to find out if each customer is eligible for a daily deal (Y) or not (N), based on. (*Hint - use a nested if function)
Monday 20% off total bill
Tuesday Free dessert
Wednesday Mains - Buy 1 Get 1
Thursday 10% off total bill
Friday No deal
Saturday No deal
Sunday Free side order please help on this problem
My bonus for the first employee results in 600
I checked the formula like a 1000 times, can't find the mistake
Try =IF(AND(B2>=9,C2="yes"),B2*100,B2*50)
It can be tough, so make sure the commas are in the right spots.
sir i hvae a query on my excel sheet plz resolve i will send you excel report
Thanks
hi sir, wat are the diffrence and & or ?? plz explain
in "AND" all (2 or more) conditions MUST be true; in "OR" any one condition can be true (all conditions NEED NOT be true).
Why put in an and function
In the screen shot how can i tell excel to leave the cells "#DIV/0!" blank whenever #DIV/0! shows up? These cell are linked to another worksheet which are formulas that i dont want seen if there is no data entered
%
Grade
%
Grade
%
Grade
Language
#DIV/0!
#DIV/0!
0
F
#DIV/0!
#DIV/0!
Mathematics
73
B
60
C
68
C
Your demo file can't be downloaded, as you are asking for $ 6 per month. Infact these demo files should be freely available , as others are doing.
Thanks for the suggestion. Hopefully creating your own, working demo file based on the instructions will suffice.
Thank you!!
could you post a video of below example and tag me. thank you in advance to relief me from my pain ;)
🧡
Nice
not working
=IF(G22>=75,"Passed",IF(G22>=85,"Good marks",IF(G22>=86,"VG marks")))
I want that to fix the limitation means 75-85 should be passed
Abdul Ahad Samadi work from big to small instead...
=if(G22>=86,"VG Marks",if(G22>=85,"Good Marks,if(G22>=75,"Passed","Fail")))
Abdul Ahad Samadi work from big to small instead...
=if(G22>=86,"VG Marks",if(G22>=85,"Good Marks,if(G22>=75,"Passed","Fail")))
thank you :)
Hi, your tutorial is good, but still not able to figure out how to apply it in my excel.i am creating a salary calculator. I need three logic.
Logic A = if advance is between 2000-5000 then flat 1500 should be deducted from salary
Logic B = if advance is < 2000 then flat 500 should be deducted from salary.
Logic C = if advance is >5000 then flat 3000 should be deducted from salary
How to go about it.
Thanks
SUPERB! THX
excellento
why person 8 is getting bonus. his experience is "7 years" and certification is "No"
Every person gets a bonus in that example but there are two cases
1- If a person works atleast 9 years AND has a certification --> Multiply years worked by 100
2- if they did not work 9 years or if they dont have certification --> Multiply years worked by 50
person 8 fell into the second category
nice
Hello Chand
Ande se fonngum
bad