Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I want to format a rule in which if the amount in a cell exceeds a number, it will transwer over the rest to another row cell.
Solved by V. U. in 49 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 02/02/2018 - 01:14
Hi
Excelchat Expert 02/02/2018 - 01:14
Good day! Welcome and thank you for choosing Got It Pro-Excel.
User 02/02/2018 - 01:14
HI i need some help
Excelchat Expert 02/02/2018 - 01:14
I got your query, however I would like to clear a few points in it.
Excelchat Expert 02/02/2018 - 01:15
Yes.
User 02/02/2018 - 01:15
I want to consolidate the names
Excelchat Expert 02/02/2018 - 01:16
Okay. Kindly explain clearly your requirements.
User 02/02/2018 - 01:16
then make a rule in which if the amount in amount goes over 29,200, it will put the rest into another colum
Excelchat Expert 02/02/2018 - 01:17
Okay. In what particular cells do want that formula to be placed?
User 02/02/2018 - 01:18
for example, If i consolidate this list by names, it will put alice wang together. it would add up to 56,200. I need the amount row to not exceed 29,200 then it will put the rest, 27,200 in another row called amount 2
Excelchat Expert 02/02/2018 - 01:19
Okay. Please give me time to look into it.\
User 02/02/2018 - 01:19
thank you
Excelchat Expert 02/02/2018 - 01:20
Do we need to make another worksheet for the consolidation?
User 02/02/2018 - 01:20
no
User 02/02/2018 - 01:20
I know how to consolidate the names
User 02/02/2018 - 01:21
i do not know how to make a rule in amount that if exceeds 29,200, the rest of the amount would be put into another cell
User 02/02/2018 - 01:21
for example, when i consolidate 2 names, it will equal to 58,400
User 02/02/2018 - 01:22
then after i do this i want to make a rule saying that anything after the limit will be put into another colom cell
Excelchat Expert 02/02/2018 - 01:22
Okay. Its just simple IF statement.
User 02/02/2018 - 01:22
how do i do that?
Excelchat Expert 02/02/2018 - 01:22
Please wait. I'll write it for you.
User 02/02/2018 - 01:23
thank you
Excelchat Expert 02/02/2018 - 01:23
Please hold on.
User 02/02/2018 - 01:24
i want to limit the amount collum to 29,200 and if anything is exceeded, it will be transfered to the following row
User 02/02/2018 - 01:24
Thank you!
Excelchat Expert 02/02/2018 - 01:28
I'll send you a file.
User 02/02/2018 - 01:28
ok
Excelchat Expert 02/02/2018 - 01:30
[Uploaded an Excel file]
Excelchat Expert 02/02/2018 - 01:30
Please open it and let me explain to you how it works.
User 02/02/2018 - 01:30
ok opened
Excelchat Expert 02/02/2018 - 01:30
Col A contains the amount.
Excelchat Expert 02/02/2018 - 01:31
Col B contains the Amount 1
Excelchat Expert 02/02/2018 - 01:31
Col C contains Amount 2.
User 02/02/2018 - 01:32
ok that makes sence
Excelchat Expert 02/02/2018 - 01:32
If Column A is less than or equal to 29,200, then Col B will just copy it. And if Col A is more than 29200, Col C will have the difference of Col A and 29200.
User 02/02/2018 - 01:33
so if i make a list of 10,000 consolidation, so i need to make the formula for each one?
Excelchat Expert 02/02/2018 - 01:33
No. Just copy the formula.
Excelchat Expert 02/02/2018 - 01:34
Just drag the formula down to the last record.
User 02/02/2018 - 01:34
where do i input the formula?
Excelchat Expert 02/02/2018 - 01:34
Please wait.
User 02/02/2018 - 01:35
theres 10 formulas and they all have diffrent collums
User 02/02/2018 - 01:35
where do i input the formula?
Excelchat Expert 02/02/2018 - 01:35
A'll apply it to the online spreadsheet you uploaded.
User 02/02/2018 - 01:36
ok
Excelchat Expert 02/02/2018 - 01:36
In in the spreadsheet you have, I put the formula in I2.
Excelchat Expert 02/02/2018 - 01:37
But it seems that the spreadsheet is not yet consolidated.
Excelchat Expert 02/02/2018 - 01:37
I guess a new worksheet must be created first.
Excelchat Expert 02/02/2018 - 01:38
A worksheet that contains the sum of the amount from same contributors.
User 02/02/2018 - 01:39
look on worksheet 2 please
Excelchat Expert 02/02/2018 - 01:39
Okay.
User 02/02/2018 - 01:39
any way i can make it a rule in H?
User 02/02/2018 - 01:40
so that it sends a limit of 29,200 in I and the rest in J?
Excelchat Expert 02/02/2018 - 01:40
Okay. Please wait.
Excelchat Expert 02/02/2018 - 01:44
Okay. Please see Sheet 2.
User 02/02/2018 - 01:44
ok
Excelchat Expert 02/02/2018 - 01:45
Is it good?
User 02/02/2018 - 01:45
yes its perfect
User 02/02/2018 - 01:46
do I need to set this rule for every individual cell?
User 02/02/2018 - 01:46
or can i just set it in the row?
Excelchat Expert 02/02/2018 - 01:46
No.
User 02/02/2018 - 01:46
how can i make this a rule in the colum*
User 02/02/2018 - 01:46
my bad
Excelchat Expert 02/02/2018 - 01:46
Just pull the small square on the lower right of the selected cell.
Excelchat Expert 02/02/2018 - 01:47
In that case, you can copy the formula down to the last record.
Excelchat Expert 02/02/2018 - 01:47
Or you can just do the Copy-Paste commands.
User 02/02/2018 - 01:48
ok and it will auto go in that one?
Excelchat Expert 02/02/2018 - 01:48
Yes.
Excelchat Expert 02/02/2018 - 01:49
Are we good now?
User 02/02/2018 - 01:49
so if i copy and past comands it would not be a problem?
User 02/02/2018 - 01:49
yes thank you!
Excelchat Expert 02/02/2018 - 01:50
Yes. The formula will work on every row of your worksheet.
Excelchat Expert 02/02/2018 - 01:50
Do you have further questions?
User 02/02/2018 - 01:50
and all i have to do is just copy and past it in every cell?
User 02/02/2018 - 01:50
when i copy and past it, the H# stays the same
Excelchat Expert 02/02/2018 - 01:51
Yes. But you're not gonna do that one by one.
User 02/02/2018 - 01:51
then how do i just do it in the whole row? sorry im not good at this
Excelchat Expert 02/02/2018 - 01:52
Please wait. I'l make screenshots for you.
User 02/02/2018 - 01:53
im trying to figure out how to make it a rule for everyone in the colum instead of doing it cell by cell sorry
Excelchat Expert 02/02/2018 - 01:55
[Uploaded an Excel file]
Excelchat Expert 02/02/2018 - 01:55
Please see the image.
User 02/02/2018 - 01:57
ok i clicked on it
User 02/02/2018 - 01:57
where do i paste the forumula?
User 02/02/2018 - 01:58
and what forumula?
User 02/02/2018 - 01:58
since the amount 1 and 2 have 2 diffrent ones
Excelchat Expert 02/02/2018 - 01:59
Yes. They have different formula. They are not shown, but when you select those cells with formula and drag the FILL HANDLE down the formula will be automatically copied to the cells below.
User 02/02/2018 - 02:00
ok
User 02/02/2018 - 02:00
where do i paste the formula?
Excelchat Expert 02/02/2018 - 02:01
For Amount1, it is initially attached to Cell I2. For Amount2, it is initially attached to Cell J2.
Excelchat Expert 02/02/2018 - 02:02
Select I2 and J2, then drag the FILL HANDLE and then the formula attached to those cells will be automatically pasted to the cells below.
User 02/02/2018 - 02:02
ok that makes so much
User 02/02/2018 - 02:02
that is actually really awesome!
User 02/02/2018 - 02:02
thank you so much!
User 02/02/2018 - 02:02
Hope you have an amazing day!
Excelchat Expert 02/02/2018 - 02:02
Wow. I am happy to help you.
User 02/02/2018 - 02:03
also thank you for being patient with me!
Excelchat Expert 02/02/2018 - 02:03
Thank you so much for your cooperation. Please give your honest rating on our service. I hope to have you again in Got It Pro-Excel. Have a good day ahead.
User 02/02/2018 - 02:03
5/5!!!
Excelchat Expert 02/02/2018 - 02:03
No problem. Thank you again.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.