Mrinmoy Roy

Hello Mr. Mejon,
Unfortunately, there is no VBA function that calculates the probability of area left to a Z score in a skewed distribution.
However, I’m suggesting you some functions that might help you.

Z.TEST function >>> Returns the one-tailed probability-value of a z-test.
KURT function >>> Returns the kurtosis of a data set.
GAUSS function >>> Returns 0.5 less than the standard normal cumulative distribution.
F.DIST.RT function >>> Returns the F probability distribution.
SKEW.P function >>> Returns the skewness of a distribution based on a population: characterization of the degree of asymmetry of a distribution around its mean.

Regards!

Hi Brenda,
Follow this tutorial to fetch all the data tables from a web page. After selecting a particular data table, click on the Transform Data command to modify your data table in the Power Query Editor. There you can remove all the unnecessary columns and keep your desired data. Then hit the Close & Load button to bring the transformed data table into your worksheet.
Thanks.

Hi Ron,
You can see that yellow square with a red arrow in Microsoft Office Professional Plus 2016. In Excel 2019, you won’t find that because there’s no need to use the yellow box. Excel will automatically detect all the tables and make a list of ’em. All you need to do is, simply select any of the tables that you want to import and then load them directly into your Excel worksheet.
Thanks!

Hi KEITH,

Your problem is partly vague I think. Still, I’ve tried to build a formula that might work for you. If this doesn’t work, I would recommend you share your workbook with me or at least share a sneak peek of your dataset.

Now use this formula:

=IF(ISBLANK(K2),SUMIF(I2:I13,”asphalt field”,N2:N13),SUM(J2:J13,L2:L13))

Thanks!

Hi Michelle,
You can try the following piece of code:

Sub PasteAcrossSheets()

Dim arr(3)

i = 0
For Each Worksheet In ActiveWorkbook.Sheets
Worksheet.Activate
arr(i) = ActiveSheet.Name
i = i + 1
Next

yy = ActiveWindow.RangeSelection.Address
Set xx = Application.InputBox(“Insert a range:”, “Microsoft Excel”, yy, , , , , 8)
If xx Is Nothing Then Exit Sub

mm = Application.ScreenUpdating
Application.ScreenUpdating = False
xx.Copy

Sheets(arr).Select

Range(“G5”).Select
ActiveSheet.Paste

Application.CutCopyMode = False
Application.ScreenUpdating = mm

End Sub

Hi CHRIS,
Thanks for this interesting question. It’s not about adding multiple COUNTIFS functions but multiple COUNTIF functions inside one IFS function.

Look at the following formula. It will look for two keywords “MTT” and “GL” across the text. If it finds MTT then the output will be “MTT Exists!”. For “GL” the output will be “GL Exists!”.
If nothing matches, it will return “No Results Found!”.

=IFERROR(IFS(COUNTIF(B5,”*MTT*”),”MTT Exists!”,COUNTIF(B5,”*GL*”),”GL Exists!”),”No Results Found!”)

Regards!

Hi Andrew,
It happens because of the variable types. The two variables X & Y currently have the variable type “Long” and “Integer” respectively. To get a sum value up to 2 decimal places, make both variable types “Double”. This will reserve the decimal places.

Here’s the modified code:

Function SumColoredCells(CC As Range, RR As Range)
Dim X As Double
Dim Y As Double
Y = CC.Interior.ColorIndex
For Each i In RR
If i.Interior.ColorIndex = Y Then
X = WorksheetFunction.Sum(i, X)
End If
Next i
SumColoredCells = X
End Function

I hope this will work. Regards!

Hi SAM,
The first formula: =LOOKUP(2,1/($B$5:$B$12=$B$15),$C$5:$C$12) returns #N/A error for a lookup value that cannot be found. Thus, you can add the IFERROR function to tackle this issue.
For example use the following formula to return “Null” instead of #N/A error: =IFERROR(LOOKUP(2,1/($B$5:$B$12=$B$15),$C$5:$C$12),”Null”)
I hope this is what you were looking for.
Regards!

Hi Larry Kanzia,
Inverted commas can be single – ‘x’ – or double – “x”. They are also known as quotation marks, speech marks, or quotes.
You can get a single inverted comma just by pressing the comma button next to the ENTER button on your keyboard. To insert a double inverted comma, press and hold the SHIFT key, then press the comma key next to the ENTER button.
Thanks!

Hello Mr. Masud,
You can easily solve your problem by combining the IF and AND functions.
Suppose, you have 3 values to compare in three cells C7, D7, and E7. For this instance, let’s say C7 is in sheet1, D7 is in sheet2, and E7 is in sheet3.
Now you are in sheet3 and you want to get a feedback (Yes or No) in cell F7.
All you need to do is, type the following formula in cell F7 of sheet3.
=IF(AND(Sheet1!C7=Sheet2!D7,Sheet2!D7=Sheet3!E7),”Yes”,”No”)
After that, press ENTER and you will get your required result.
Regards!