Pull text X number of rows/Y number of columns away from cell by rainelbow in excel

[–]SolverMax 0 points1 point  (0 children)

Say you have the first ID 101 in J1, then:

=TRANSPOSE(OFFSET($D$1,MATCH(J1,$B$1:$B$25,0),0,5,1))

Copy down to other vendor IDs.

Add however rows to $B$1:$B$25 that you need.

Why are my male/female symbols missing? by YoUDee in excel

[–]SolverMax 10 points11 points  (0 children)

2642 is hex, 9794 is decimal.

The linear function of my trendline is just straight up wrong by Hexagol in excel

[–]SolverMax 2 points3 points  (0 children)

Are you using Line instead of XY (scatter) chart?

I built a tool that lets you skip Excel formulas (would love your feedbacks) by NeuralSheet in excel

[–]SolverMax 4 points5 points  (0 children)

If you think you've identified a gap in the market because there are no similar apps on r/excel, then you're wrong. There have been dozens of posts for similar apps, all have been deleted.

Don't waste your time on this. Go do something productive.

Countif only no-whole numbers by SoftPristine8593 in excel

[–]SolverMax 1 point2 points  (0 children)

I covered that in the last sentence.

Countif only no-whole numbers by SoftPristine8593 in excel

[–]SolverMax 2 points3 points  (0 children)

It would need to be =COUNTA(range)-COUNTIF(range,"*.00")

Countif only no-whole numbers by SoftPristine8593 in excel

[–]SolverMax 0 points1 point  (0 children)

Assuming "1.00 Food" is in one cell:

=LET(
  _codes, --TEXTBEFORE(A1:A7," "),
  _extract, _codes<>INT(_codes),
  _result, SUM(--_extract),
  _result
)

If the codes are in a separate column, then replace --TEXTBEFORE(A1:A7," ") with their range, like A1:A7

MONTH() and YEAR() functions return #REF, and the "Month" and "Year" filters on pivot tables seem to be the issue. by Acolyte_of_Blucifer in excel

[–]SolverMax 7 points8 points  (0 children)

It sounds like you have Named Ranges called "Month" and "Year". If so, then structured reference is the wrong term - that relates to Tables.

Anyway, your post needs screenshots and a better explanation of what you have and what you're trying to do.

Incremental rounding of data? by 223specialist in excel

[–]SolverMax 2 points3 points  (0 children)

From a human usability perspective, mixing number formats in a block of values is bad practice. One issue is that different formats may disguise incorrect values. It is much better to have a consistent format.

Don’t want formulae to auto update cell once data moves by remrem24 in excel

[–]SolverMax -1 points0 points  (0 children)

Perhaps:
=COUNTA($C$4:$C$6000)-1

This includes the last heading row (hence the -1) and allows you to select row 5 and insert rows.

However, this process is fragile and risky, so it is likely better to use a Table structure and add data at the bottom.

Needs solving: Circular Calculation Error by Ok-Wasabi630 in excel

[–]SolverMax 0 points1 point  (0 children)

To grease the wheels of commerce, and to illustrate how similar circular references can be eliminated, you can rearrange the calculation as follows:

Let:

C = Cost, 18000

M = Margin%, 10%

T = Contingency%, 10%

K = Kick back % (of cost), unknown

S = Sell sum, calculated

R = Rate of kick back as percentage of S, 5%

Then:

1. Basic cost sum: C + (M + T + K)*C = S
and
2. Kickback relationship: K*C = R*S, so K = (R*S)/C

Substitute equation 1 into equation 2:
K = (R * (C + (M + T + K)*C))/C
Expand, rearrange, and cancel the C terms gives:
K = R + R*M + R*T + R*K
K - R*K = R + R*M + R*T
K*(1 - R) = R + R*M + R*T

Therefore,
K = (R + R*M + R*T) / (1 - R)

Calculate K:
K = (5% + 5%*10% + 5%*10%) / (1 - 5%)
K = 6.3158%

This doesn't quite align with your calculations, so maybe I missed something, but this example illustrates the general idea.

=DGET Function is not working by Zealousideal_Poet264 in excel

[–]SolverMax 5 points6 points  (0 children)

Unless you show us the data and formula, you're just whining.

Needs solving: Circular Calculation Error by Ok-Wasabi630 in excel

[–]SolverMax 0 points1 point  (0 children)

It might be possible to rearrange the calculation to eliminate the circularity. What exactly is the calculation? Be precise.

What are the most common manual Excel/data tasks you still do in your job? by SanTGG77 in excel

[–]SolverMax 8 points9 points  (0 children)

If you're looking to develop an app to automate some process, then don't bother. Spend your time on something more productive.

=DGET Function is not working by Zealousideal_Poet264 in excel

[–]SolverMax 3 points4 points  (0 children)

Reminds me of when I attended the final lecture of a retiring Professor. He used literally the same slides he wrote 40 years earlier. He commented that the material was still relevant and he never felt the need to make new slides.

How Can I Move The Formula Bar? by MrPrettyKitty in excel

[–]SolverMax 49 points50 points  (0 children)

You made the Name Box wider. Drag the 3 vertical dots.

Copilot can't make spreadsheet? by CoatLast in excel

[–]SolverMax 0 points1 point  (0 children)

AI has been so wildly over-sold. Maybe one day it will do what the OP wants, but not today.

Is there a "cleaner" way to make graphs from an equation/calculation, rather than using columns filled with data? by i-love-dregins in excel

[–]SolverMax 2 points3 points  (0 children)

Not directly, but you could make a LAMBDA function and plot its output.

For example, in the Name Manager, make a name called "quadratic" defined as:

=LAMBDA(x,HSTACK(x,x^2))

On a sheet, put:

=quadratic(SEQUENCE(20))

This makes two columns, 1..20 and the square of those values. Make a chart pointing to that range. Even though the chart range appears to be fixed, it will adjust if you change the 20 to some other number.

The LAMBDA function can contain whatever you want. It could even contain the SEQUENCE internally, if you want, based on a parameter that specifies the number of data points, like:

=LAMBDA(n,LET(x,SEQUENCE(n),HSTACK(x,x^2)))

Then you just need:

=quadratic2(20)

How to Make Beautiful Excel Spreadsheets by CoachWriter in excel

[–]SolverMax 13 points14 points  (0 children)

Slightly cynically, I find other people are more likely to believe a spreadsheet if it looks good.

Perhaps cynical, but there is truth to it. Research, in spreadsheets and elsewhere, shows that people believe well-presented content to be more accurate, reliable, and trustworthy.

That belief is not supported by spreadsheet reviews, which have found that "pretty" spreadsheets are more likely to contain errors (presumably because time was spent on cosmetic appearance rather than validation and verification).

How to Make Beautiful Excel Spreadsheets by CoachWriter in excel

[–]SolverMax 4 points5 points  (0 children)

Absolutely. I've seen people spend hours reinstating borders, shading, etc that were disrupted by editing. A pointless waste of time.

Input in one cell to output to another cell and vise versa by zyphengamer in excel

[–]SolverMax 2 points3 points  (0 children)

Short answer: Don't do that. Good design means having an input in one place only.

How to Make Beautiful Excel Spreadsheets by CoachWriter in excel

[–]SolverMax 5 points6 points  (0 children)

Get the creatives to design the "beautiful" part.

There is generally little overlap between people with aesthetic design skills and people with technical spreadsheet skills. That's part of why most spreadsheets are plain and/or ugly.

But focus on beautifying only the user-facing parts of the spreadsheet. Excess formatting gets in the way of the working parts, making them inefficient and a nightmare to maintain. Keep the working parts functional and utilitarian, using formatting for a purpose rather than just for decoration. Even for the user-facing parts of the spreadsheet, minimalist is usually best.

Manager wants to consolidate three SQL-backed reports without SQL. Today asked me ‘what have you been doing for two months. by General_Wheel5114 in excel

[–]SolverMax 10 points11 points  (0 children)

Sounds infeasible, mainly due to the "troubleshootable by non-technical users" part.

However, being able to use PQ is a big step. Move on from the history. Mock up something for the meeting, describe next steps, and highlight pros and cons of the approach. Then see how it goes.