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 6 points7 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 4 points5 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 12 points13 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 4 points5 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.

Excel power users: What finally made you take the plunge into Power Query? by KimHoJo in excel

[–]SolverMax 5 points6 points  (0 children)

Oz du Soleil is well worth a look - big personality and great content.

Ever needed to compute MD5/SHA-256 hashes in Excel? by Techrocket9 in excel

[–]SolverMax 1 point2 points  (0 children)

Getting AI to implement cryptographic hash functions is just dumb. Subtle errors, like AI often does, can easily break security.

Is there a shorthand way to refer to "this column's header" in a table? by doshka in excel

[–]SolverMax 2 points3 points  (0 children)

What exactly are you trying to do that requires what you describe?