• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to multiply values only after sum is reached

J

johnnybrazo22

New Member
A B

1

5

2

5

7 14

2 4


Say I want to multiply every value of column A by 2 (into column B) that comes after the sum of column A has passed 10, so only 7 and 2 - and leave the rest of the values alone. Does anyone know how I can put this thought into a formula?
 
@oldchippy

Regarding the data posted, shouldn't be the below formula for cell B2? If range started in row 1 without a title and the formula were for B1 cell, it'd appear a 10 before the 14.

=IF(SUM($A$1:A1)>10,A2*2,"")

Regards!


EDITED
 
A B

1. 1

2. 5

3. 2

4. 4 4

5. 7 14

6. 2 4


Thanks! This is a great forum. I know this will make this formula a bit more complicated but what if I wanted to multiply every value past the sum of 10. So, only 2 of the 4th row.
 
Hi, johnnybrazo22!

Did you read my previous post? At column B you want to get the original value or blank if sum of column A don't exceed 10? And where does your data start, in A1 or in A2? If in A1 those formulas (both) don't work.

Regards!

PS: Your new change of 4 4 in row 4 isn't clear, please elaborate.
 
@SirJB7,


I did do that formula first, but I read the question again and thought that he need A1 in the formula, but obviously that's not the case.
 
Hi Sir7B7,


I want column B's values to be blank if the sum of column A doesn't exceed 10.


My data actually starts in A3 and goes to A286.


The example: So after row 4, the sum is 12. If possible, I just wanted to multiply every value that comes AFTER the sum of 10 is reached. Therefore, multiply by 2 only half of the 4 in row 4.


Does that make sense?
 
Hi, johnnybrazo22!

=SI(SUMA(A$3:A3)>10;MIN(SUMA(A$3:A3)-10;A3)*2;"") -----> in english: =IF(SUM(A$3:A3)>10,MIN(SUM(A$3:A3)-10,A3)*2,"")

Regards!
 
SirJB7,


That worked! Is there way to message you? That formula went right over my head ha. There is one more facet to the formula that I need to account for. There will be numbers in column A that I want to contribute to the sum of 10 (the real sum is $5,000,000) but I don't want multiplied by 2. How can I include that?
 
Hi,


I'm not sure I understand exactly what you require, so given the previous sample


A B

1. 1

2. 5

3. 2

4. 4 4

5. 7 14

6. 2 4


Can you now give the result you are looking for, thanks
 
Hi, johnnybrazo22!

If you want to post an email address here I'll get back to you. There's no built-in messaging system in this website and even if firstly I posted my email I later decided to update that policy, as I wrote many times:

http://chandoo.org/forums/topic/locking-the-cell-automaticall-once-the-date-passed#post-21903

Regards!
 
@oldchippy

Hi!

The input data is the 1st column (without dot): 1,5,2,4,7,2, from cell A3:A8.

The output desired is 2nd column: blank,blank,blank,4,14,4, from cell B3:B8.

The accumulated from first entry are: 1,6,8,12,7,2.

The accumulated excess over 10 are: -9,-4,-2,2,9,11.

So he wants to double any excess over 10: blank,blank,blank,4=(4-2)*2,14=7*2,4=2*2

That's were my formula enters in the game: =IF(SUM(A$3:A3)>10,MIN(SUM(A$3:A3)-10,A3)*2,"")

Regards!
 
If any OP needs to communicate in private they should post their email address, if they do not wish to make their normal email address use or open a gmail email address.
 
@b(ut)ob(ut)hc

Hi!

Aren't those open email addresses the normal ones? I usually don't want to use neither sirjb7@cia.gov nor sirjb7@sis.gov.uk nor sirjb7@anonymous.org.

Regards!
 
Private messaging isn't necessary, just thought it might be easier to show an example of what I'm trying to accomplish...Nevertheless I will try my best to illustrate it here so that everyone can observe and learn.


Row 1: Red 6

Row 2: Green 5

Row 3: Blue 11

Row 4: Red 6 6

Row 5: Green 3 6

Row 6: Blue 2

Row 7: Red 8 16


Note the first row is A3.


In the example, I would like column C to output x2 of column B's values, once the sum of 25 is reached. The tricky part is that I need the value's whose row begins with "Blue" to contribute to achieving the 25 sum but never to multiply by 2 (before or after 25 is reached).
 
Hi, johnnybrazo22!


Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).


If you consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.


Despite of this, try this adaptation of previous formula:

=SI(A3="Blue";"";SI(SUMA(B$3:B3)>25;MIN(SUMA(B$3:B3)-25;B3)*2;"")) -----> in english: =IF(A3="Blue","",IF(SUM(B$3:B3)>25,MIN(SUM(B$3:B3)-25,B3)*2,""))


Regards!
 
Good morning SirJB


By normal I meant private/family, but I will use either your cia.gov or sis.gov from now on :)
 
@b(ut)ob(ut)hc

Hi, old dog!

Good noon for you too.

I think you'd prefer the sis.gov address...

Regards!
 
You must log in or register to reply here.
Back
Top