Help With Excel Formulas
-
- PlatinumLounger
- Posts: 3714
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Help With Excel Formulas
Thanks so much for that explanation. Not sure I'll retain it but the good news is I did understand what you explained.
Regards,
hlewton
hlewton
-
- PlatinumLounger
- Posts: 3714
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Help With Excel Formulas
Need help again and I hope my explanation is clear enough.
I am trying to create a formula and can’t get it right. I am trying to put the formula in cell K3 using the value in cell J3. However, the value in J3 can be a value of 1,2,4, or 5. Values of 4 or 5 are calculated by the formula J3-3 because the value in cell J3 should be 3 but is blank if it is OK and has a value of 3. This value can be changed by increments of 1 BUT only in a forward direction. In other words, the value could increase to 5 and then start over at 1. So, I tried to make a formula that says if J3 is less than 3, then J3 must first subtract 3 from 5 and then add whatever the number is in J3 to that value. So, if J3 has a value of 2 the steps to get to that value would require a move of 4 steps. Two examples of the formulas I tried are attached but I cannot get them to work as I need. What needs corrected to make this work? Thanks
I am trying to create a formula and can’t get it right. I am trying to put the formula in cell K3 using the value in cell J3. However, the value in J3 can be a value of 1,2,4, or 5. Values of 4 or 5 are calculated by the formula J3-3 because the value in cell J3 should be 3 but is blank if it is OK and has a value of 3. This value can be changed by increments of 1 BUT only in a forward direction. In other words, the value could increase to 5 and then start over at 1. So, I tried to make a formula that says if J3 is less than 3, then J3 must first subtract 3 from 5 and then add whatever the number is in J3 to that value. So, if J3 has a value of 2 the steps to get to that value would require a move of 4 steps. Two examples of the formulas I tried are attached but I cannot get them to work as I need. What needs corrected to make this work? Thanks
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton
hlewton
-
- 5StarLounger
- Posts: 794
- Joined: 24 Jan 2010, 15:56
-
- Administrator
- Posts: 77569
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
You don't need so many parentheses:
=IF(J3<3,5-3+J3,J3-3)
or, somewhat shorter:
=J2+IF(J3<3,2,-3)
=IF(J3<3,5-3+J3,J3-3)
or, somewhat shorter:
=J2+IF(J3<3,2,-3)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3714
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Help With Excel Formulas
Thank you. I really thought I had tried the first one above but apparently I didn't since your works great.
Regards,
hlewton
hlewton
-
- PlatinumLounger
- Posts: 3714
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Help With Excel Formulas
I used your first formula that is bold above and thought I could figure out how to make the K3 cell remain blank if nothing was in J3. I tried a few things one which was this =IF((J3<3,5-3+J3,J3-3),"") Why isn't that working for me. I looked at many of your formulas from the past that I saved just for this kind of thing and I still can't figure it out. Again, what correction do I have to make to your formula above to make the cell appear empty unless J3 has a value in it?
Regards,
hlewton
hlewton
-
- Administrator
- Posts: 77569
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
You have to add an extra IF to check whether J3 is blank:
=IF(J3="","",IF(J3<3,5-3+J3,J3-3))
You can also use the IFS function:
=IFS(J3="","",J3<3,5-3+J3,J3>=3,J3-3)
=IF(J3="","",IF(J3<3,5-3+J3,J3-3))
You can also use the IFS function:
=IFS(J3="","",J3<3,5-3+J3,J3>=3,J3-3)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3714
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Help With Excel Formulas
Thank you. I thought I needed an extra IF but just didn't get it right. Strange thing is I can understand it AFTER you design it but can never seem to figure it out on my own. Thanks for your help.
Regards,
hlewton
hlewton
-
- PlatinumLounger
- Posts: 3714
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Help With Excel Formulas
I have a question about a formula that is working perfectly, with Hans’ help a while back. I do not want to change anything about the formula. However, it makes me question what I thought I knew about an Absolute Cell Reference. I’m glad it worked out the way it did but it’s not what I thought would happen. I thought I’d have to edit the Absolute Cell References, but I didn’t.
If you look at the 2 formulas pasted below you can see that the $ signs are referencing both the row and cell location. You can also see in one formula they are “$J$1173:$J$1773” and in the other formula they are “$J$1302:$J$1902.” I needed to move the range that the formulas refer to down in the spreadsheet and even though they are Absolute Cell References, the range in the formula updated.
Admittedly I am not great at finding things on the NET and did not find an answer as to why the Absolute Cells updated themselves. My guess is that even though they are Absolute Cell References they will still update themselves if the range they are associated with moves. Is that correct?
=IFERROR(CONCATENATE(ROUND(MAX($J$1173:$J$1773),0)," & ",ROUND(VLOOKUP(MAX($J$1173:$J$1773),$J$1173:$K$1773,2,FALSE),0)),"")
=IFERROR(CONCATENATE(ROUND(MAX($J$1302:$J$1902),0)," & ",ROUND(VLOOKUP(MAX($J$1302:$J$1902),$J$1302:$K$1902,2,FALSE),0)),"")
If you look at the 2 formulas pasted below you can see that the $ signs are referencing both the row and cell location. You can also see in one formula they are “$J$1173:$J$1773” and in the other formula they are “$J$1302:$J$1902.” I needed to move the range that the formulas refer to down in the spreadsheet and even though they are Absolute Cell References, the range in the formula updated.
Admittedly I am not great at finding things on the NET and did not find an answer as to why the Absolute Cells updated themselves. My guess is that even though they are Absolute Cell References they will still update themselves if the range they are associated with moves. Is that correct?
=IFERROR(CONCATENATE(ROUND(MAX($J$1173:$J$1773),0)," & ",ROUND(VLOOKUP(MAX($J$1173:$J$1773),$J$1173:$K$1773,2,FALSE),0)),"")
=IFERROR(CONCATENATE(ROUND(MAX($J$1302:$J$1902),0)," & ",ROUND(VLOOKUP(MAX($J$1302:$J$1902),$J$1302:$K$1902,2,FALSE),0)),"")
Regards,
hlewton
hlewton
-
- Administrator
- Posts: 77569
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Help With Excel Formulas
An absolute reference will not change when you fill the formula down or to the right, nor when you copy the cell with the formula and paste it elsewhere.
But it will change if you move the range referred to:
But it will change if you move the range referred to:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3714
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Help With Excel Formulas
Thanks I kind of thought that was how it worked since I didn't have to manually edit the formulas.
Regards,
hlewton
hlewton