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.
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'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.
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.
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
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 :-)
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.
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.
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 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.
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
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?
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
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)
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...
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
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.
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?
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
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.
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
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.
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
Very amazing tutorial,have be struggling to understand the use of OR and AND but not have got everything right... thumbs up
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...
Beautifully explained!
Simplified and Straightforward
Awesome, He's very thorough in explaining each step of the process, I greatly appreciate that. Very Helpful
Really simple and easy method to teach excel formulas, well done
Keep revisiting this so must be a good sign
Really helpful. I like your thinking, really simplifies it.
helped me so much❤️❤️❤️
Thank u sir...very helpful video...tomorrow is my computer practical xam...helped me a lot
simple and clear
Wow. Great in explaining it. I used to have problem doing it. Thanks
Thanks a lot. The lesson is very easily understandable.
Easy to understand tutorial, thanks!
THANK YOU SO MUCH!!! Some heroes don't wear capes!
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 .
Thanks so much for this tutuorial! It was hugely helpful to me!
Ralph, thank you so much, you doing a marvelous job.
Nice explanation
Thanks
Very important lesson and well explained, Thank you!
Helpful video Sir... Thank u 🙏🙏
Thank you so much.....it works👍
Ralph, your videos are great! Thank you!
Dude, you missed your calling, great teacher and instructor.
Thanks a lot
Nicceee... Thank you. 😊
Very useful
Thanks
love your tutorials !!!!
Fantastic...
THANKS for great help for double logic ...
I was wondering why no one double clicks to auto fill? I've watch several tutorials and everyone click and drag.
Good video
Very helpful. Nice. Thanks for the tutorials!
very clear - thank you
Thank you, very easy to follow. You helped me figure out my work stuff.
So helpful! Thank you
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.
excellent
Well explained
🧡
Thank you!
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%
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.
man this Video was soo helpful thankx bro u did a great job out there
Great work
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.
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.
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.
so the first problem was an if(and(or...)) function and the second problem was an if(or(and...)) function?
very helpful
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
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 :-)
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.
Nice
Hi can I get the original question somewhere so that I can practice this? Thank you for a great tutorial
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.
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
Thank you!!
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.
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
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?
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
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)
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...
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.
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
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
pls if i have number between two value like 0.55
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.
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?
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
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.
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
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.
Why put in an and function
thank you :)
SUPERB! THX
sir i hvae a query on my excel sheet plz resolve i will send you excel report
excellento
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
=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")))
can any one help to formulate an equuation using if
When and where i can use and or or logical_test?
Can you help me to resolve some issue with "IF" function please
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
nice
Can a CountA function be used in an IF function? Thanks
I figured it out, thanks, it's CountIF
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
could you post a video of below example and tag me. thank you in advance to relief me from my pain ;)
is it possible have or and and in one if function
Yep
Done
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).
not working
Hello Chand
Ande se fonngum
bad