# Search:

Type: Posts; User: johnmpl; Keyword(s):

Page 1 of 6 1

1. ## Thread: Total by PuestoTrabajo (Wrong Calculation)

by johnmpl
Replies
0
Views
365

### Total by PuestoTrabajo (Wrong Calculation)

Hi, to all members of this great forum.

The idea of ​​the table that I present in the file is to...
2. ## Thread: Special Unpivot Columns

by johnmpl
Replies
3
Views
360

### Re: Special Unpivot Columns

If you remove duplicates, Descript28 is related to THING 1, when this must be in THING 2. Same thing with Descript55, must be with THING 3. How can I get the correct...
3. ## Thread: Special Unpivot Columns

by johnmpl
Replies
3
Views
360

### Special Unpivot Columns

Hi, to all!

I need to special unpivot some data.

Input:

<colgroup><col span="3"><col><col><col><col><col><col></colgroup><tbody>
NAMEA
NAMEB
4. ## Thread: Generate N random numbers between A and B, sum up to X with special condition

by johnmpl
Replies
4
Views
151

### Re: Generate N random numbers between A and B, sum up to X with special condition

No... there is no factor. Is "random" because you could get any value between 0 and 1 (with several decimals, if you needed). But is "random with conditions",...
5. ## Thread: Generate N random numbers between A and B, sum up to X with special condition

by johnmpl
Replies
4
Views
151

### Re: Generate N random numbers between A and B, sum up to X with special condition

No...

I know that RAND() function give a random number between 0 and 1... but this numbers must give a specified sum by column, depends on the given vector:

In...
6. ## Thread: Generate N random numbers between A and B, sum up to X with special condition

by johnmpl
Replies
4
Views
151

### Generate N random numbers between A and B, sum up to X with special condition

Hi to all!

I bring you a challenge to do without the help of macros or Excel tools (like Solver). The idea is to use only Excel formulation, and, if possible, use the same formulation for the...
7. ## Thread: VBA to transpose rows to columns by 'n' times

by johnmpl
Replies
4
Views
1,321

### Re: VBA to transpose rows to columns by 'n' times

Hi, to all!

Another option could be:

Sub TransposeWithRep()
Dim n&, q&

n = Application.InputBox(" Times to repeat? ", , 2): If n <= 0 Then Exit Sub
With...
8. ## Thread: Find a,b,c then Replace with a.jpg,b.jpg,c.jpg...etc..

by johnmpl
Replies
1
Views
82

### Re: Find a,b,c then Replace with a.jpg,b.jpg,c.jpg...etc..

You can try this little vba code:

Selection = Evaluate("transpose(transpose(" & Selection.Address & " & "".jpg""))")
End Sub

First select the range...
9. ## Thread: Distinct Count based on two criteria (time involved)

by johnmpl
Replies
6
Views
223

### Re: Distinct Count based on two criteria (time involved)

Hi, honestabe1982!

Try:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col...
10. ## Thread: sum of product array with multiple values in multiple columns - Non VBA

by johnmpl
Replies
14
Views
854

### Re: sum of product array with multiple values in multiple columns - Non VBA

Hi, lpvdsteen!

You can try:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col...
11. ## Thread: average multiple criteria

by johnmpl
Replies
3
Views
203

### Re: average multiple criteria

Hi, to both of you!

Another options could be:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color:...
12. ## Thread: correction formula given increase every 2 months

by johnmpl
Replies
14
Views
955

### Re: correction formula given increase every 2 months

Sorry, my mistake. Check this formulae:
=IF((B4>0)*(B2>B4),MIN(INT(DATEDIF(B4,B2,"y")/6),5)&" anno/i - "&2*MOD(MIN(DATEDIF(B4,B2,"y"),30),6)&" mese/i","")
13. ## Thread: correction formula given increase every 2 months

by johnmpl
Replies
14
Views
955

### Re: correction formula given increase every 2 months

No... IFERROR is a function for Excel 2007 and newer.

You can use:
=IF((B2>0)*(B4>B2),MIN(INT(DATEDIF(B4,B2,"y")/6),5)&" anno/i - "&2*MOD(MIN(DATEDIF(B4,B2,"y"),30),6)&" mese/i","")

Blessings!
14. ## Thread: Numbers and cell values in a Formula

by johnmpl
Replies
2
Views
121

### Re: Numbers and cell values in a Formula

Hi, ktobrien!

In a matrix constant no cell references are allowed.

For your particular example, you can use:

=SLOPE({0,1}*F4,{0,1}*J3*60)

Blessings!

by johnmpl
Replies
3
Views
184

### Re: Index Match?

Hi, to all!

Another option could be:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color:...
16. ## Thread: Nested if formula

by johnmpl
Replies
20
Views
453

### Re: Nested if formula

Hi, to both of you!

With the new changes, you can use:

=F4*IF((J4<>"per diem")*(YEAR(C4)=2017),(MONTH(C4)<6)*(1.75%-MONTH(C4)*0.25%))

Blessings!
17. ## Thread: correction formula given increase every 2 months

by johnmpl
Replies
14
Views
955

### Re: correction formula given increase every 2 months

Try:
=IF(B4,IFERROR(MIN(INT(DATEDIF(B4,B2,"y")/6),5)&" anno/i - "&2*MOD(MIN(DATEDIF(B4,B2,"y"),30),6)&" mese/i",""),"")

Blessings!
18. ## Thread: correction formula given increase every 2 months

by johnmpl
Replies
14
Views
955

### Re: correction formula given increase every 2 months

Hi, again!

Is this? (in B6) :
=IF(B4,IFERROR(INT(DATEDIF(B4,B2,"y")/6)&" anno/i - "&2*MOD(DATEDIF(B4,B2,"y"),6)&" mese/i",""),"")

Try and comment! Blessings!
19. ## Thread: correction formula given increase every 2 months

by johnmpl
Replies
14
Views
955

### Re: correction formula given increase every 2 months

Hi, max_max!

Place 2 or 3 handwritten examples of the result you want to get, so that we can help you better. Blessings!
20. ## Thread: Nested if formula

by johnmpl
Replies
20
Views
453

### Re: Nested if formula

Hi, to all!

If you have this message, you use Excel 2003 or earlier.

You can use this formulae instead:
=F4*IF((J4<>"per diem")*(YEAR(C4)=2017),MAX(0,1.75%-MONTH(C4)*0.25%))

Blessings!

by johnmpl
Replies
5
Views
396

### Re: Multiple combinations

Hi, to all!

If your data is in column A begins in A1, and have variable data, and you want the results in column B:

Sub Result()
Dim a&, Data, i&, j&, d&

a =...
22. ## Thread: Help with formula...if I knew how to correctly word it, I may already know how to fix it

by johnmpl
Replies
3
Views
214

### Re: Help with formula...if I knew how to correctly word it, I may already know how to fix it

You can try too:

=-HomeAway!D5/HomeAway!G25% + 200 * (-HomeAway!D5/HomeAway!G25%>100)

Blessings!
23. ## Thread: Using VLOOKUP and SUMIFS at the same time

by johnmpl
Replies
2
Views
259

### Re: Using VLOOKUP and SUMIFS at the same time

Hi, codemancode!

You could try this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color:...
24. ## Thread: Finding the highest number and notate that in another column

by johnmpl
Replies
6
Views
141

### Re: Finding the highest number and notate that in another column

Hi again!

With Volatile functions:

A
B
C
25. ## Thread: Dividing negative numbers for result 0

by johnmpl
Replies
4
Views
223

### Re: Dividing negative numbers for result 0

Hi, to all!

Check this:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col...
Results 1 to 25 of 150
Page 1 of 6 1