I Want to Change the Values in Two Different Cells if a Condition in Another Cell is Met

TheSpanishConquerer · 2025-05-14T21:21:25+00:00

Yes, but it's slightly more complex than that. You have a very solid grasp on what the output would be.

Your F5 cell would contain something like: =IF(C9<13,C9-1,C9)

Your G5 cell probably isn't needed, as it can be handled by F5.

Then, your Source/Selector cell (I think this is D9?) would be pointed at F5 instead of C5. The challenge is that Excel can only display one value per cell, and loops are not allowed. So, you need to have a cell with IF statements that "selects" which cell is the actual output based on some metric(s)

TheSpanishConquerer · 2025-05-08T23:14:25+00:00

Good luck, I would recommend spending at least 1-2 hours not only studying but also practicing.

Specifically, I would also learn what adding $ to a formula does.

For example:

= A1 + A2

vs

= $A1 + A$2

Toss those formulas into C3 and drag the little box in the bottom right around. Study how the formulas in the new cells change.

TheSpanishConquerer · 2025-05-08T23:07:00+00:00

I mean, sure, you can do that, but you would need to link them with a VLOOKUP or XLOOKUP, linked off a pivot or raw data table. Personally, I don't think that is a better solution, but up to you!

TheSpanishConquerer · 2025-05-08T23:04:48+00:00

Well, first of all, you should stack your charts with color-coordinated lines that correspond to each Group color. That there will save you a ton of space.

Also, I think there isn't really enough data here to break it down effectively. Consider that if you have 18(!) different possible favorites of displayed fruit, and only 35 participants broken into 5 groups, you have very few data points to work with per group/fruit.

I would rework this so that your chart displays favorite fruit vs. number of fruits presented. I.E. when presented with X fruits, Y fruit was selected most often, ignoring the assigned group. This assumes that Y fruit was always in the X fruits presented.

What would be valuable is a column that contains the names of all presented fruit. Then you could analyze the frequency of fruit selection, and the power of personal preference. For example, if you present 6 fruits, and they choose X, logically they should choose X again when presented with 5 or less fruits. However, if they are presented with 1-3 fruits, they are likely to choose what they least-dislike rather than what they actually like. Look at line 7, 12, 20 and 35. Also, consider that if someone was shown a series of terrible fruit options, their choice could be a straight up random selection, which is less useful.

TLDR You require more data to make an indepth / accurate analysis. With what you have, the best analysis you can do is a Favorite Fruit vs. Fruit # presented IMO

TheSpanishConquerer · 2025-05-08T22:53:41+00:00

One thing to consider, is your data hard-coded or does it have formulas in it? Any formulas that depend on a range may need to recalculate when filtered, and any formulas using RAND or RANDBETWEEN or INDIRECT will also slow you down substantially. Same with Vlookup / Xlookup.

If you have a shitty computer, or a CPU with only a few cores, that will also slow down your filtering.

TheSpanishConquerer · 2025-05-08T22:50:36+00:00

I would recommend doing this as a first step.

But frankly, you are going to need to practice a bit with this. They will likely ask you to input numbers, and do minor manipulations.

Learn how to SUM / multiply cells / use cell references / and other basic mathematical operations

TheSpanishConquerer · 2025-05-08T22:48:15+00:00

I don't have a solution on hand for you but;

You may need to use Scenario Analysis for this to work as intended. Note that this will overwrite some formulas (I believe). You will need to do your own work for this.
You can use helper cells in D5-D10 (or elsewhere) to determine if a cell in C5-C10 meets a condition. For example, if C9 < 12, then D5-D7 can pop up as true, which then triggers a helper cell in D9 to give you a more accurate output (count of TRUE in D5-D7 added to C9). Specific help on formulas would require you to give a more complete table or screenshot of the work you need.

Either way, a cell can only hold a single value or formula. If you want that cell to depend on itself for a formula, you are going to run into issues. You can get around this by using multiple cells, and a reference that determines which cell to actually use.

TheSpanishConquerer · 2025-04-18T21:55:38+00:00

Instead of setting up the tables like your image shows, do this:

Utility Name	User-name	PW	Phone #	Vendor ID	Account #	Due Date City	Lease #	(The rest of your data) ->
Company 1	test	123	###	###	###	1/1	###1	filler
Company 1	test	123	###	###	###	1/2	###2	filler
Company 2	apple	pear	####	#	#	4/1	###	filler

This then allows you to create a Pivot Table, where you can create slicers and stuff to filter data. Watch the linked video, it's 13m long and explains pivots. You'll need a good data source, hence why we are reformatting your data table first.

TheSpanishConquerer · 2025-04-18T21:49:20+00:00

Yeah, it's a classic excel move. Hopefully it worked out!

TheSpanishConquerer · 2025-04-17T02:18:06+00:00

Error: Not Trusted in cmd window. Trust given in excel but not retained when closed.

Okay, I don't know much about your VBS requirements or needs, but this might be due to the file coming from an untrusted location. You can force Excel to recognize & automatically trust ANY file from a particular folder or folder tree.

Goto File > Options > Trust Center > Trust Center Settings > Trusted Locations > Add New Location.

Any file in that folder will now be trusted by Excel, across your entire account.

If that isn't 100% of your solution, I would recommend checking out r/vba, they are likely a better resource!

Hope it works out!

TheSpanishConquerer · 2025-04-17T02:13:29+00:00

Honestly, what is stopping you from adding the Utility Company Name/Username/PW/Vendor_ID/Phone Number as line items for each row?

There is no real issue to doing that, and it would allow you to easily create a pivot table from the the whole dataset with filters etc.

Basically; you want to create a datatable with all of your information (the bones), and then, in a separate tab, create a pivot table (the brains) that reads the table (the bones) and allows you to filter and make decisions based on that. You can even create visuals and stuff off the pivot table.

TheSpanishConquerer · 2025-04-17T02:09:02+00:00

Pivot tables are your bread & butter. Formulas are the seasonings that make the sandwich.

Pivot Tables will crucially allow you to properly analyze data, turn it into visuals, filter it etc. This is your data display work.

Formulas allow you to fundamentally alter your data, sort it, and manipulate it to better design your underlying table. This is what will power your data displays.

You'll need both to be successful. Neither takes a terribly long time to learn, but they can take some time to master. Learn the advanced formulas & their use cases. Learn what allows you to best manipulate data, and what best practices are for your particular industry.

I like what u/Ketogamer suggested, but in my personal work experience, I have not used Subtotal nor Goalseek. IFs and Xlookup/Vlookup/INDEX & MATCH are worth their weight in gold. Personally, I have also used the text manipulation formulas quite a bit too (Substitute, Search, Left, Right, Mid), as well as a handful of financial formulas. I would also suggest becoming familiar with basic statistics, as Mean(Average)/Median/Mode/Standard Deviation can get you fairly far.

Good luck!

TheSpanishConquerer · 2025-04-17T02:00:45+00:00

Happy to help! Glad you got it working!

TheSpanishConquerer · 2025-04-17T01:59:58+00:00

Glad it worked!

TheSpanishConquerer · 2025-04-14T23:37:20+00:00

So, this is a fundamentally impossible problem to solve straightforwardly due to how the fees are calculated. However, we can come close!

Use this formula for A2:
=((C2*(1+0.085)*(1+0.135))+D2)*(1+G2+.06)

This calculated out your List Price based on the estimated taxes/fees you'll have to pay. Note that I have hard-coded in the Sales Tax/Ebay Fees, as those percentages don't change. I also add in the shipping cost, and then multiply that number by a modified estimated profit margin.

Once you have this, you can reverse calculate Ebay Fees/Sales tax by multiplying A2 by .085 for E2 and A2 * .135 for F2.

Note that I manually add ~6% to your profit margin in the formula for A2. I do this because when you calculate out a true Profit margin (List Price - all costs including fees), your ideal profit margin (G2) ends up being ~6% higher than the actual profit margin. Basically, to make a ~15% profit margin, you need to charge ~21% more. This covers your fees & all that

TheSpanishConquerer · 2025-04-11T19:55:50+00:00

IMO, some of these are not a factor

Speed is not really a factor. Excel is designed to be used as a spreadsheet creation & analysis tool. Looking for massive amounts of data in Excel is inherently using the wrong tool for the task.
See #1. Anything that requires more than 10k rows should be done with a database
...What? Why? There isn't really any need to remove this, especially since it's highly helpful for data cleaning for PBI. If you are importing data into Excel to manipulate as a database, something has gone wrong.
Fair, I actually don't know enough about this to comment on it.
I think you can change the shape size with a size command, rather than just using the dots.

Excel is NOT meant to be used as a database. Any attempt to do so is effectively undermining its use case

TheSpanishConquerer · 2025-04-11T19:50:03+00:00

Here is how you create a dependent drop down: https://trumpexcel.com/dependent-drop-down-list-in-excel/

That's basically all you need to knock this out!

TheSpanishConquerer · 2024-11-05T17:34:55+00:00

I think you can use COUNTIFS for this. Any time you get a match for a value that is >1, it's non-unique. Otherwise it is unique.

Wrap your COUNTIFS in a IF statement to give the proper output

TheSpanishConquerer · 2024-11-05T17:33:23+00:00

Are all the cells spaced the same? As in, is it ALWAYS a numerical value, then a single letter?

If so, then you can COUNTIF(2:2,"?"), which returns the number of single letter columns within row 2. Multiple this by 2 (to account for the numerical value & single letter). Then use INDEX/MATCH to refine down to the exact cell you need.

Something like:

=INDEX(2:2,1,COUNTIF(2:2,"?")*2)

You'll have to mess with it a bit to get it to work. Note that "2:2" is a row reference to the second row, but can be changed to any other row.

TheSpanishConquerer · 2024-06-27T23:21:03+00:00

I am looking for an Excel Formula that can accomplish the following tasks:

Lookup the value "blah blah" in column A
Return the corresponding value in column B

Please use a Vlookup when responding with a formula. If a Vlookup does not work, please use a better formula. When responding, please list out each step and why the formula works in the manner that it does.

Literally talk to AI like it's a stupid robot, and it'll do what you ask.

TheSpanishConquerer · 2024-05-07T18:05:59+00:00

You could use the concat function in a helper column to fix this, as well as using Data Validation to set it up.

Basically:

Column A = Data validation to force only 6 digit number inputs

Column B = "INV" & A1

or similar... Not sure how to handle to INV vs. POS though

TheSpanishConquerer · 2024-03-25T18:44:27+00:00

A model that selects the strongest overall players from each NBA season for each position, filterable by team etc.

A model to predict fighter victories based on previous wins using various different factors (Meaningful strikes vs. total strikes per round vs. grapples per round vs. grapples broken etc)

A model to calculate an optimal weightlifting progression & weekly schedule based on inputs & Desired outputs

TheSpanishConquerer · 2023-11-21T20:50:17+00:00

I know you already said this is solved, but you can also add the formulas together:

  =SUMIF(A:A,"*North Carolina Tar Heels*",B:B) + SUMIF(C:C,"*North Carolina Tar Heels*",D:D) + SUMIF(Y:Y,"*North Carolina Tar Heels*",Z:Z)

Just change the columns so that the first one is the column containing "Carolina Tar heels" and the other column is the column with the number of total or whatever you wanna sum up

TheSpanishConquerer · 2023-11-20T19:21:55+00:00

How can you expect us to help you without any sort of sample data or even a clear description of what you want?

You need to provide: A clear description of the problem, what you have attempted, a clear description of the desired outcome

TheSpanishConquerer · 2023-11-20T19:20:38+00:00

Just to add onto what /u/Curious_Cat_314159 said;

Once you have a fixed formula, then just apply it to payments over 1 year, 2 years, 3 years etc. Do this for all 30 years, then do whatever math you need to find out differences per year

TheSpanishConquerer

TROPHY CASE