Great video; thanks! One kinda subtle point that might confuse viewers -- OR() is "one or more" of the conditions is true. In the video, you only talk about if (exactly) one of the conditions is true.
Teacher...Great Instruction! And ohhh so Timely. It does seem odd to me that Google Sheets ArrayFormula does not play well with AND / OR Functions without Trickery!?
Thanks for your videos! Is there a way to assign a value (or point system) to words? In your example could you assign a value of 1 point to each "complete" text in order to get a total of all "complete" jobs?
What would be the best way to check a different spreadsheet for the max/highest number and then return the name that corresponds with the highest number
Technically, when you multiply two boolean values your spreadsheet will automatically convert those to 1 & 0. I like to use N function for clarity when things get convoluted.
I'm looking for a way to have data entered into one Google worksheet and stored in another. I think this would be a database function, but not sure. I live stream hockey games and post in the stream who scored using spreadsheet script. During intermission, a recap of the players that scored is shown. What I want to do enter the data on one sheet and have it stored on another sheet. Which video series would be best to review for what I'm looking to do?
nice, your info is useful for me. but i have a question, how about the formula OR in array if add up two or more criterias such as true + true = 2? thanks before
this was complicated 😢 i wish i could understand, i think i need more fundamental understanding of the basics, because I'm have difficulty following along
HELP! Haha I've been going around in circles with this problem I want to calculate a value between 15-25 (11 in whole), but I don't want the formula to calculate anything above 25 For example, if the data is A1=28, then I only want the result as 11 (max value) and multiply it by 1000, anything above 25 will be multiplied by 2000 but not including
How can I convert this to an array formula to drag it down? =MINIFS(A:A,A:A>C1,B:B>D1) Column A - Dates (sorted) Column B - Corresponding values Cells C1, C2, etc. - LookUp Date Cells D1, D2, etc. - LookUp Value Basically I want the first date (A:A) after my LookUp Date (C1) that has a bigger value (B:B) than my LookUp Value (D1). edit: I also know I could just filter the list and search for the minimum value in this "virtual array", but still the problem stands of not beeing able to drag the formula down manually. I have about 8'000 rows of data and more to come. It's not feasable to drag everything down, it's too much of a calculation...
@@ExcelGoogleSheets I will see if I can do this and lookup how to create one. But thanks for the input! Great video as always btw! You helped me alot with data analysis and statistics. :)
It should work in Office 365 without a custom function though, but then since you're asking about dragging down with array, I guess it's safe to assume you are asking Google Sheets question.
@@ExcelGoogleSheets Yeah it was about Google Sheets unfortunately. But nevertheless thanks for your answer. I will try to find a goo workaround that doesn't take that much computing power.
=ARRAYFORMULA(TEXTJOIN(" / ",TRUE,IF((and(emailcol=emailcell,statuscol,"pending")),orderidcol&"/"&statuscol&" £"&valuecoll""))) Can anyone help with my formula please. What i'm trying to do is exclude pulling all orders though "OrderID/Status £Value / " for status of "pending" but all I get is blank can you use it like this, it works fine intill I add the and formula
@@ExcelGoogleSheets That was my fear. I have formatting in the cell. I can't figure out how to work around this. Ultimately, I want the cell to be blank unless there's something in one/any of the prior cells, then I want to sum those.
![퍼주는게 많아 비밀이 많은 집 84타입을 이 가격에 준다고? 😱돈도 다 내드림... 헐.. (No.2010) [인천신축분양]](http://i.ytimg.com/vi/PF71CQdayHc/mqdefault.jpg)
This person is a truly good teacher, his explanations and instructions are precise and easy to understand.
This person Thanks You :)
100PER CENT
best excel tutorial i've seen. You are the only one that covers arrays in if statements
Great video; thanks! One kinda subtle point that might confuse viewers -- OR() is "one or more" of the conditions is true. In the video, you only talk about if (exactly) one of the conditions is true.
Nice very usefull. thank you for teaching.
Was very much useful to formulate for a priority summary prep task
Great Explanation....would be very useful. if you leave a sample file for practice of the above file...
I learnt something new today 19:13 thanks
I love the way you explained the differences.. Great video. Thank you!
Thanks for watching!
Thanks your all videos useful
Sunil Jaipur India 🇮🇳
Perfection!! Thank you.
Thank you for your lessons, you are a generous teacher ❤️
awesome video! very well explained!
Great video as always. Thought this was basic skills, but got surprised with the binary calculation. Thank you for bringing the good stuff.
Teacher...Great Instruction! And ohhh so Timely. It does seem odd to me that Google Sheets ArrayFormula does not play well with AND / OR Functions without Trickery!?
I LOVE YOU MAN!!! best channel ever !!!!!!
:)
very nice explain
How would I do a formular for Tuday and -2 from days left and minus -1 if it is a thursday?
great one!
This is just too awesome
Thank you! Big help.
Thank you! Helped me!
Amazing, Can Not Thank you enough
Awesome video, thanks! Any advice on how to sort the spreadsheet above by date and customer into a separate tab ? I can’t seem to get it right
Use SORT function
Thanks for your videos! Is there a way to assign a value (or point system) to words? In your example could you assign a value of 1 point to each "complete" text in order to get a total of all "complete" jobs?
What would be the best way to check a different spreadsheet for the max/highest number and then return the name that corresponds with the highest number
You are a very good instructor, why in the first example you didn't use N when you did multiplied the two Boolean?
Technically, when you multiply two boolean values your spreadsheet will automatically convert those to 1 & 0. I like to use N function for clarity when things get convoluted.
Thank you 🎉
Regular Viewer, Learning a Lot
Best regards from New Delhi india🇮🇳
Sip. .... Mantab. .....
This helped me so much, thank you!!
great video thanks a lot mate
It's a good 👍👍
Well explained, thanks
I want to put the word 'Excellent' into C9 if the number 82 is in B9 and I'd like to use conditional formatting. How do I do this?
I'm looking for a way to have data entered into one Google worksheet and stored in another. I think this would be a database function, but not sure.
I live stream hockey games and post in the stream who scored using spreadsheet script. During intermission, a recap of the players that scored is shown.
What I want to do enter the data on one sheet and have it stored on another sheet.
Which video series would be best to review for what I'm looking to do?
Maybe this? ua-cam.com/video/ZKYvrD-3Ksc/v-deo.html
many, many thanks
where can i get this sheet to practice?
nice, your info is useful for me. but i have a question, how about the formula OR in array if add up two or more criterias such as true + true = 2? thanks before
It would still work. He did mention any result that is not 0 will result in true.
THANK YOU!!
👍
Great
Hi brother, I tried but my formula not working, can you please help?
this was complicated 😢 i wish i could understand, i think i need more fundamental understanding of the basics, because I'm have difficulty following along
any one know who to do if greater than equal to or less than to make is give a text value? I'm using the second sheet for the values
Please rephrase your question. It doesn't make much sense.
HELP! Haha I've been going around in circles with this problem
I want to calculate a value between 15-25 (11 in whole), but I don't want the formula to calculate anything above 25
For example, if the data is A1=28, then I only want the result as 11 (max value) and multiply it by 1000, anything above 25 will be multiplied by 2000 but not including
Wait I got it...
=IF(AND(A1>= 15,A1=26,11000+((A1-25)*2000),0))
Might not be the simplest formula, but it works haha
16' AND / OR function dont ork with array. How to solve this.
Well. Did you watch the video?
@@ExcelGoogleSheets Yes, I watched it. Very helpful. I love your vídeos. Thank you for your time.
How can I convert this to an array formula to drag it down?
=MINIFS(A:A,A:A>C1,B:B>D1)
Column A - Dates (sorted)
Column B - Corresponding values
Cells C1, C2, etc. - LookUp Date
Cells D1, D2, etc. - LookUp Value
Basically I want the first date (A:A) after my LookUp Date (C1) that has a bigger value (B:B) than my LookUp Value (D1).
edit: I also know I could just filter the list and search for the minimum value in this "virtual array", but still the problem stands of not beeing able to drag the formula down manually. I have about 8'000 rows of data and more to come. It's not feasable to drag everything down, it's too much of a calculation...
Custom function.
@@ExcelGoogleSheets I will see if I can do this and lookup how to create one. But thanks for the input! Great video as always btw! You helped me alot with data analysis and statistics. :)
It should work in Office 365 without a custom function though, but then since you're asking about dragging down with array, I guess it's safe to assume you are asking Google Sheets question.
@@ExcelGoogleSheets Yeah it was about Google Sheets unfortunately. But nevertheless thanks for your answer. I will try to find a goo workaround that doesn't take that much computing power.
you are a f'ng machine!
=ARRAYFORMULA(TEXTJOIN(" / ",TRUE,IF((and(emailcol=emailcell,statuscol,"pending")),orderidcol&"/"&statuscol&" £"&valuecoll"")))
Can anyone help with my formula please.
What i'm trying to do is exclude pulling all orders though "OrderID/Status £Value / " for status of "pending" but all I get is blank can you use it like this, it works fine intill I add the and formula
U answered the question sorry they don't work with ARRAY
Correct.
Why does mine keep giving a ZERO in the spot. I thought i coded it to leave blank: =IF(OR(GG9="",FR9="",FB9="",EK9=""),GG9+FR9+FB9+EK9,"")
Either you need to remove formatting from that column or one of those cells you check are not really blank.
@@ExcelGoogleSheets That was my fear. I have formatting in the cell. I can't figure out how to work around this. Ultimately, I want the cell to be blank unless there's something in one/any of the prior cells, then I want to sum those.
I love you
:)
13:00
COGS cost of goods sold, ok
IF Functions from Hell ua-cam.com/video/6cwZoKdZh94/v-deo.html
SUMIF, COUNTIF OR Criteria ua-cam.com/video/cmtF5ulh6mo/v-deo.html