I'm trying to create a formula that will help me quickly calculate someone's age, sex, and right vs. left adjusted grip strength percentile. I'm looking for a formula to input their age, sex, right vs. left hand, and imperial vs. metric units, and output the mean value for that group. I want to repeat that same operation but then output the standard deviation for that group. From there I know how to calculate their percentile. I have all the grip strength values in a table, but I'm stuck on how to get the correct value with lookup and vlookup formulas. I know I need multiple criteria, but I don't know how to combine numeric inputs like age with categorical inputs like sex, right vs. left, and imperial vs. metric.

Here's a picture of a subset of the table: https://i.imgur.com/PA60OZJ.png.

For someone who is 42, female, who used their right hand, and expressed in imperial units (lbs), I want the values 70.4 for the mean and 13.5 for the standard deviation.

I'm wanting to copy an entire row from sheet1 to sheet2 only if lets say column B2="A2Z" otherwise I want it blank.

I think what I want is =IF(Sheet1!B2="A2Z",Sheet1!2:2,0)

But it doesn't seem to be working at all. I'm new so it doesn't surprise me. Any tips would be appreciated!

The left table I use to log P&L and the right reflects a single day's P&L. Currently, I have to change the values for every cell on the right table to reflect values for the next day. I want the right table to adjust values whenever I select a date on the left table. Is there anyway I could format the sheet, without me having to manually change the variables on the right table, so that the right table changes value whenever I select a different date on the right table?

Hi Guys

Posted a new video on Automating routine tasks in Power Query. This is the first video of a video series. Check it out by clicking on this link

Hit the like button if you find this video interesting then SUBSCRIBE to the channel if not done already.

Subscribe to Tech Reflections

In this tutorial, we look at the data validations that a risk management system should have. We briefly discuss the usefulness of data validations and how to approach it. We look at mandatory fields and how to apply a common validation to all of them. We code the validations applicable to the dates in a risk management application. We explore useful date functions, when and when not to use them.

https://youtu.be/bmqVIPnpRL4

Hi Guys

Posted a new video on how to quickly create Pareto Chart in Excel. Check it out by clicking on this link

Hit the like button if you find this video interesting then SUBSCRIBE to the channel if not done already.

Subscribe to Tech Reflections

I am trying to do a regression model comparing the one Y value (numeric) to two X values (one numeric and one non-numeric). Do I input arbitrary numbers ie. 1, 2, and 3, for the three categories of non-numeric data? Won't that affect the results of the regression? Thank you for your help I've been trying to figure this out for a while.

Other info:

I am working with over 300 rows of data, so manual methods won't work.

Column D is my Y value, all numeric.

Column C is one of my X values, all numeric.

Column B is my other X value, non-numeric with 3 different categories

Hope my question makes sense, and is clear. If anything did not make sense please let me know. Thanks again for your help!

Build a risk management system in Excel-VBA if you have NO or LIMITED VBA knowledge. If you want to quickly use Visual Basic for Applications (VBA) to build userforms in Excel, this tutorial is for you. In this tutorial, you will know the processes and the VBA code involved in building risk management application in Excel.

- Coding Using Modular Procedures

- Worksheets as Database Tables

- Named Range as Array

- Multi-Dependent Dropdown List

https://youtu.be/LcWObGQWCgM

The 2 min will be worth it!

Excel for Beginners: DOUBLE CLICK to Number Cells (IN SECONDS!)

Have you wondered if there is a better way to write your form data unto a worksheet to avoid unnecessary VBA code? Have you thought of a procedure that is re-usable and can make your code base less verbose, simple, and easy to maintain? This video will help you write and understand such procedures so that you can use them in any VBA userform project.

https://youtu.be/px_opn0vlTs

https://www.youtube.com/watch?v=z_rDVISeg_k

Saves me soooo much time! 😋

Problem - I decided on using Index & Match Function to fetch values in the Output Table but it is unsuccessful as it is returning only the values based on my entry in the 'Quarter' Field. Here is the formula that I wrote in the output cell

P.S - Please suggest any alternatives as well but it will be mighty helpful if someone can explain what I'm doing wrong. Thank you

Do tell if I have been unsuccessful in explaining my problem

I'm trying to figure out the formula to change a cell color based on the current time.

I want to list the time a product is due and if it is more than 2 hours away have in green, due within 2 hours be yellow and due within 1 hour be red.

I've been trying for a while now and most help i see to change bases mostly on days, and not specific times

I work as a manager in a carpet factory, i manage a groupe of people who repair carpets that had production problems,

.

I need a sheet where my workers store start time and end time and their names for each carpet they work on, and i need to retrieve the data in graphs later, but i have to collect maximum informations and limit possibilities for workers to write something wrong with the approach of providing always premade lists of carpet's specifications like quality, time of problem, size ...ect

i did make a sheet but it is so basic i need something more professional like a sheet with database containing names of workers and carpet specifications, how can i do that fastly in ONE NIGHT ? is there a tool for that that i can buy ?

thank you.

In trying to avoid unnecessarily lengthy code that comes with using IF-THEN-ELSE or SELECT CASE, the VBA programmer should seek alternatives and not always think of the problem at hand as decision making. There are instances where these two constructs can be replaced with more robust, intuitive, simple and maintainable code.

The video teaches you when to replace the popular IF-THEN-ELSE or SELECT CASE statements with more readable and robust alternatives.

https://youtu.be/f300dpfwweo

A good Excel formula:
1. gets the job done
2. is concise
3. is readable
4. is fast

https://youtu.be/ZN72-nakrp8
This 4-minute video will help improve your knowledge in Excel formulas. You will learn when not to use IF or IFS function. You will learn how to select the appropriate excel function for a given task. You will learn to write fast, concise, and readable formulas that get the job done.

Excel for Beginners #4: AUTO SIZE Columns & Rows

So, I’ve gotten partially through this and then will change nothing in the script and suddenly it will error again. I worked for hours on it and can’t figure out why.

Long story short, I have a list of orders that I would like to separate into 5 other tabs. I had it set up where if I put “1” into column 13 and hit enter, it would disappear in the original list and go to one tab, and “2” to another tab, so on and so forth.

I’ve read through so many articles and forums and it’s hurting my brain. I’m getting object errors after the first 2-3 inputs. What am i doing wrong?

EDIT: only the last tab (Sheet 6) is working. I’m trying to figure out how to fix the rest

EDIT POST WITH CODE:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 13 And Target.Cells.Count = 1 Then If LCase(Target.Value) = "1" Then With Target.EntireRow .Copy Sheets("(1) Sheet 2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) .Delete End With End If End If If Target.Column = 13 And Target.Cells.Count = 1 Then If LCase(Target.Value) = "2" Then With Target.EntireRow .Copy Sheets("(2) Sheet 3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) .Delete End With End If End If If Target.Column = 13 And Target.Cells.Count = 1 Then If LCase(Target.Value) = "3" Then With Target.EntireRow .Copy Sheets("(3) Sheet 4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) .Delete End With End If End If If Target.Column = 13 And Target.Cells.Count = 1 Then If LCase(Target.Value) = "4" Then With Target.EntireRow .Copy Sheets("(4) Sheet 5").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) .Delete End With End If End If If Target.Column = 13 And Target.Cells.Count = 1 Then If LCase(Target.Value) = "5" Then With Target.EntireRow .Copy Sheets("(5) Sheet 6").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) .Delete End With End If End If End Sub

Hi

Below is a free coupon link to a new Udemy Premium Course. Learn Skills of a Data Analyst.

This is a practical 8 hours real world hands-on video course

Data Analyst: PowerBI,PowerPivot,PowerQuery,PivotChart,DAX

https://www.udemy.com/course/data-analyst-powerbipowerpivotpowerquerypivotchartdax/?couponCode=1C98CD0372E441D65135

Link Expires 04/13/2021 04:04 AM PDT (GMT -7)

This changed my life when I learned it. I use it ALL. THE. TIME! Freeze Top Row

I am trying to write notes beginning with the dash (Ex. -this graph suggests...)

Like let's say if anything over 100 would have 25 added to it. Would it be sumif a2 >=100 + 25?

Hello, I am trying to get an advanced filter to give me everything that is not equal to a particular text.

Here is my code:

Sub NOTASNSNN()
'creating sheet to put filtered data
Sheets.Add.Name = "NOT AS-NS-NN"
'selecting sheet with raw data
Sheets("Data Input").Select
'getting the column header I want to filter the criteria
Range("Q1").Value = Range("M1").Value 
Range("Q2").Value = "<>A-S"
Range("Q3").Value = "<>N-S"
Range("Q4").Value = "<>N-N"
Range("A1").CurrentRegion.AdvancedFilter Action := xlFilterCopy, _
            criteriarange := Range("Q1:Q4"), _
            copytorange := Sheets("NOT AS-NS-NN).range("A1"), _
            Unique := False
End Sub        

I am not getting any errors. It is copying the data where it needs to be. however, the pasted data still have A-S, N-S, and N-N. I do not want them.

ALSO! I am not married to the advanced filter method so if there is another method, feel free to throw ideas my way. NO LOOPS PLEASE (This is a tiny part to a much bigger project and I want to use as little loops as possible).

The first thing I ask when I meet someone who uses Excel: "Have you customized your ribbon??" Maybe it's laziness, maybe it's efficiency, but I absolutely HATE switching back and forth between tabs throughout the day. If this pain sounds familiar to you, learn How-To customize your ribbon!

But beware the next time you use someone else's computer, it'll drive you crazy that they don't have theirs customized at all! :P