Create modifier on multiple cells that affects formula

[7r1ck573r]

Ok folks, how are all of you?

I have a calculator that I am making for a roleplaying game I enjoy. In it, if an item has a number of charges per day it modifies the formula for creating it.

I want to have drop down boxes beside each option that can be made into a charges per day item, which I have done, but I want it to modify a portion of the formula later on in another cell.

So something like =(IF(C2:C20)=Yes,get value from c2:c20 but only ones with yes, perform formula c2:c20/(5/number of charges per day)," ")

basically I dont know how to make modifiers for each of the 15 cells cells I am working with. It is important that each one be calculated separately because some of the cells may have information in them but they shouldn't be reduced in price unless they are charges per day.

Thanks for your time!

[FDibbins]

Hi and welcome to the forum

We would love to help you with your question, but 1st, in accordance with forum rules, please rename your thread to something more meaningful, that actually describes your problem.

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertise, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

[TMS]

Hi, welcome to the forum.

You will, almost certainly, be asked by the moderators to change your thread title to something that better describes your problem, making it search engine friendly. if you have a few minutes, have a look at the forum rules.

In the meantime, one suggestion would be to use SUMPRODUCT:

=SUMPRODUCT(--(C2:C20="yes"))


Regards, TMS

[7r1ck573r]

titled changed to the best of my abilities to describe my issue.

Will sumproduct do something from like

A1 B1 C1
4 16000 Yes
A2 B2 C2
2 2000 no
A3 B3 C3
1 2000 no

All of the b's get added together in the formula but B1 is modified by another number because it is a yes. But I want B2 and B3 not to be modified unless they also say yes.

Does that make more sense?

[7r1ck573r]

Should I use a table for all this information instead?

tables can be basically nested formulas right?

[7r1ck573r]

bit of a noob at excel but I have been learning a lot the last couple of days making this thing work.

[TMS]

Thank you for changing the thread title.

As an example:

=SUMPRODUCT(--(C2:C20="yes"),--(B2:B20*5))


If you want a specific answer, you need to explain in more detail what it is you are trying to do. The above formula will multiple the values in cells B2 to B20 by 5 but only where the matching values in C2 to C20 equal "yes".

Maybe this is more like what you need:

=SUMPRODUCT(--(C2:C20="yes"),--(B2:B20),--(A2:A20))


I'll let you work out what that does


Regards, TMS

[7r1ck573r]

Looking at my current formulas I think I found a better route to go anyways. I will let you know if it doesn't. Thanks for your help.

[TMS]

Whatever.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[7r1ck573r]

Actually can I use this same method to figure out which of the items are the largest number and keep it the same, then find the second highest and multiply it by 75% and then find the rest and multiple them by 50% and then spit all of that data added together into a cell?

[TMS]

No idea. You seem to imagine that I know what your data looks like and how it is laid out. You can do many, if not most, things with a little thought and application.

To get the largest and second largest you could use LARGE. Armed with that data, you could sum the rest of the data and subtract the highest two.

So, in broad terms, you'd have

=LARGE(range,1)+LARGE(range,2)*75%+(SUM(range)-LARGE(range,1)-LARGE(range,2))*50%


Regards, TMS

[7r1ck573r]

the data would be from one column spanning 12 rows. If for instance C1 was 16000,c2 was 4000, C3 was 1000, and C4 was 2000 then C1 would be 100% C2 would be 75% and c3 and c4 would be at 50%. then getting summed together it would be 16000+3000+500+1000=20500.

Does that make it specific enough? I am trying to give as much description as possible so I can create this. I appreciate everyone's help so far. Talking to a buddy who is a DB admin but he says he needs to see it in person if he is going to help.

[TMS]

In the pseudo formula I gave in the last post, change every reference to "range" to "C1:C12", no quotes ... or whatever.

=LARGE(C1:C12,1)+LARGE(C1:C12,2)*75%+(SUM(C1:C12)-LARGE(C1:C12,1)-LARGE(C1:C12,2))*50%


I understand your buddy's viewpoint. It would be a lot easier for someone to offer a solution if you uploaded a sample workbook.


Regards, TMS

[7r1ck573r]

How does one go about doing that? I could always email you the workbook or something?

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[7r1ck573r]

TMS that worked perfectly! Thank you so much! I have a bit of reworking to do because I wasn't thinking of the true scope that I needed to be on for this project but that will certainly fix the main issue I was having with that portion of the calculator.

(Thanks FD if I have any more questions and need to upload info I will know how to do that.)

[TMS]

You're welcome.