Search:

Type: Posts; User: Eric W; Keyword(s):

1. Thread: New at Excel - help with an addition/multiplication nested formula

by Eric W
Replies
5
Views
228 Re: New at Excel - help with an addition/multiplication nested formula

First, I can tell that you're pretty new to formulas. What you have is workable, but has potential for improvement. For example, none of your formulas requires the SUM function. The SUM function...
2. Thread: Calculate EXACT number to achieve an EXACT percentage

by Eric W
Replies
10
Views
436 Re: Calculate EXACT number to achieve an EXACT percentage

It's just basic algebra. You have this formula:

=(I2+I4)/(I4+O4)

that you want to set equal to G8 by changing the I2 value. This leads to this equation:

(X+I4)/(I4+O4)=G8

Now just solve...
3. Thread: Volume Tier Formula

by Eric W
Replies
14
Views
407 Re: Volume Tier Formula

Yes, that's much clearer. Try this in D7:

=MIN(D\$3-SUM(D\$6:D6),\$B7-SUM(\$C7:C7)-\$B6)

Copy across and down as needed. If you don't want to see the zeros, there are lots of ways. A formula way...
4. Thread: Index match or find the maximum matching value

by Eric W
Replies
9
Views
412 Re: Index match or find the maximum matching value

This is where it gets tricky. Well, MORE tricky! Fuzzy matching is a very inexact science, and the results you want are often very dependent on the type of data you have, plus some specific...
5. Thread: New at Excel - help with an addition/multiplication nested formula

by Eric W
Replies
5
Views
228 Re: New at Excel - help with an addition/multiplication nested formula

Glad to help! :cool:
6. Thread: Volume Tier Formula

by Eric W
Replies
14
Views
407 Re: Volume Tier Formula

Yes, that works, and I can see what you're going for, but I'm struggling a bit with the details. The volume values you've got now are not the same as in your first example, but the tier values are...
7. Thread: New at Excel - help with an addition/multiplication nested formula

by Eric W
Replies
5
Views
228 Re: New at Excel - help with an addition/multiplication nested formula

Welcome to the MrExcel forum!

=4675+MAX(0,ROUNDUP((B2-30000)/1000,0)*165)

where the weight is in B2.
8. Thread: Help, required single formula, instead of using 3

by Eric W
Replies
5
Views
162 Re: Help, required single formula, instead of using 3

K6: =IF(C6=1,IF(C5=1,K5+1,1),IF(C5<>1,K5+1,1))
9. Thread: Volume Tier Formula

by Eric W
Replies
14
Views
407 Re: Volume Tier Formula

I'm not sure what else to tell you. Maybe your browser shows the icons in a different location. If you hover your mouse over the icons, do any of them have a tool-tip that says "Table"?
10. Thread: Calculate EXACT number to achieve an EXACT percentage

by Eric W
Replies
10
Views
436 Re: Calculate EXACT number to achieve an EXACT percentage

I'm glad I could help out! :cool:

And I'm more than old enough to drink! :beerchug:

And while I appreciate the offer of a donation, I'd prefer you just pay it forward. Help out a stranger, or...
11. Thread: Copy target value across based on cell value

by Eric W
Replies
3
Views
74 Re: Copy target value across based on cell value

Sure.

=IF(OR(B\$8<\$B2,B\$8>\$B2+\$C2-1),"",\$E2)

The value in red is the week #, which it gets from row 8. That's why the \$ is used. The value in blue is the start week from \$B2. As you drag the...
12. Thread: Copy target value across based on cell value

by Eric W
Replies
3
Views
74 Re: Copy target value across based on cell value

Something like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color:...
13. Thread: Creating Random number generator but ignoring numbers given

by Eric W
Replies
8
Views
212 Re: Creating Random number generator but ignoring numbers given

To avoid getting duplicate values in D1 and D2, probably the easiest way is to put this formula in O1: =D1
Then in the D2 formula, change the M1:N1 to M1:O1 in 2 places. If you have something...
14. Thread: How do I take a total value and reduce the value of a cell and move to next until the total is met?

by Eric W
Replies
5
Views
218 Re: How do I take a total value and reduce the value of a cell and move to next until the total is met?

Open a COPY of your workbook. I assume a starting range like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse:...
15. Thread: Making the formula Dynamic based on Date Range

by Eric W
Replies
6
Views
225 Re: Making the formula Dynamic based on Date Range

Sure, same idea:

...
16. Thread: Volume Tier Formula

by Eric W
Replies
14
Views
407 Re: Volume Tier Formula

In order to use the Table tool, click on Reply to Thread, then in the bottom right, click Go Advanced. You'll see a slightly larger box with 3 rows of icons on top. Click on the first icon in the...
17. Thread: Calculate EXACT number to achieve an EXACT percentage

by Eric W
Replies
10
Views
436 Re: Calculate EXACT number to achieve an EXACT percentage

I wish you'd mentioned the M4 and O4 values earlier. It is impossible to figure out the percentage without knowing them. I tried to recreate your spreadsheet as best I could from your example and...
18. Thread: making excel recognize it's 20XX not 19XX

by Eric W
Replies
12
Views
129 Re: making excel recognize it's 20XX not 19XX

There is a Windows setting to handle this.

Click Control Panel > Time & language > Under "Related settings" on the right: Additional date, time, & regional settings > Change date, time, or number...
19. Thread: How do I take a total value and reduce the value of a cell and move to next until the total is met?

by Eric W
Replies
5
Views
218 Re: How do I take a total value and reduce the value of a cell and move to next until the total is met?

This is why I said "maybe something like this". I'm not entirely sure what your layout is like. Based on what you posted, it appeared that the top grid showed how many items were in each "bucket". ...
20. Thread: How do I take a total value and reduce the value of a cell and move to next until the total is met?

by Eric W
Replies
5
Views
218 Re: How do I take a total value and reduce the value of a cell and move to next until the total is met?

Maybe something like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color:...
21. Thread: Making the formula Dynamic based on Date Range

by Eric W
Replies
6
Views
225 Re: Making the formula Dynamic based on Date Range

Happy to help! :cool:
22. Thread: Calculate EXACT number to achieve an EXACT percentage

by Eric W
Replies
10
Views
436 Re: Calculate EXACT number to achieve an EXACT percentage

What is the formula you use to calculate the percentages?
23. Thread: Formula will not automatically adjust for different rows?

by Eric W
Replies
3
Views
69 Re: Formula will not automatically adjust for different rows?

Happy to help! :cool:
24. Thread: Formula will not automatically adjust for different rows?

by Eric W
Replies
3
Views
69 Re: Formula will not automatically adjust for different rows?

Try:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$A\$32:\$A\$34&"'!B"&ROW(A11)&":K"&ROW(A11)),"<>"))
25. Thread: Calculate EXACT number to achieve an EXACT percentage

by Eric W
Replies
10
Views
436 Re: Calculate EXACT number to achieve an EXACT percentage

Welcome to the forum.

When you calculate a percentage, you are essentially comparing 2 numbers. You only provided one number, 4,250. However, 4,681 is 67.201% of 6,965.67. So apparently,...
Results 1 to 25 of 150
Page 1 of 6 1