LinkBack URL
About LinkBacks
Hello,
Is it possible to tell a cell to use a formula i have hidden elsewhere in the document if a certain cell is less than 10, and use a different formula if the certain cell is higher than 10?
Thanks
Use formula A if cell X = less than 10.. Use formula B if cell X = higher than 10
Hello,
Is it possible to tell a cell to use a formula i have hidden elsewhere in the document if a certain cell is less than 10, and use a different formula if the certain cell is higher than 10?
Thanks
Maybe with VBA but I know nothing about it. Hopefully someone can help you.
In the cell where you want the result.
=IF(certain cell < 10, use formula, use different formula)
Hi, so simply! Thanks.Originally Posted by jason.b75
My formula is extremely long... 2 questions sir.
1. is there a limit to how long a formula can be
2. how can I add expand on this IF statement..... so for example if < 10 use formula A if > 10 use formula B if > 20 use formula C?
here is what I have got at the moment:
=IF(L110<10,(N8*'1+'!O8)+(N9*'1+'!O9)+(N13*'1+'!O10)+(N17*'1+'!O10)+(N51*'1+'!O28)+(N54*'1+'!O28)+(N101*'1+'!O50)+(N102*'1+'!O51),(N8*'10+'!O8)+(N9*'10+'!O9)+(N13*'10+'!O10)+(N17*'10+'!O10)+(N51*'10+'!O28)+(N54*'10+'!O28)+(N101*'10+'!O50)+(N102*'10+'!O51))
I have 3 more price lists to add into the mix, 20+ 50+ and 100+
Yes, if you're using Excel 2013 the limit is 8192 characters.
It sounds like you could use a look table. Tells us what ALL the levels are.2. how can I add expand on this IF statement..... so for example if < 10 use formula A if > 10 use formula B if > 20 use formula C?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Ouch!
It looks like you have one of those formulas that can't easily be shortened.
There are limits to formula length, but I don't think that you will exceed them. (8192 characters / 64 levels of nesting).
To add more statements, you would use
=IF(cell<10,first formula,IF(cell>30,second formula,IF(cell>20,third formula,fourth formula)))
with the fourth formula being the one for >10.
Note that when using < you need to order the criteria lowest to highest, when using > you need to order the criteria highest to lowest.
Also be aware of the difference between < and <= The formula in post 5 would use the second part of the formula when L110 = 10.
Thank you.
I would like to run formula A if cell C110 is less than 10
I would like to run formula B if cell C110 is 10 or higher
I would like to run formula C if cell C110 is 20 or higher
I would like to run formula D if cell C110 is 50 or higher
I would like to run formula D if cell C110 is 100 or higher
can you give me the structure for this??
Can't this original formula simply be shortened to
=O8+O9+O10+O10+O28+O28+O50+O51+(N8+N9+N13+N17+N51+N54+N101+N102)*IF(L110<N10,1,10)
Also I don't understand the excessive need for ' and ! in the formula
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
1+ and 10+ etc referenced above in other posts is different sheets within the workbook.
Oh ok thanks not seen that before
=IF(cell<10,first formula,IF(cell>100,second formula,IF(cell>50,third formula,IF(cell>20,fourth formula,fifth formula))))
Almost, each > should actually be >=
You've never seen a formula reference another worksheet before?
(N8*'1+'!O8) is no different to (N8*'Sheet 1'!O8), just that the sheet is named 1+ instead of Sheet 1.
Never seen the
'1+
reference before
I was hoping that was the case, but it just threw me that you didn't see it as a sheet reference, given that the exclamation mark preceding the cell address qualifies the preceding text as a sheet reference.
I had a volatile function in mind, something in the lines of
=SUMPRODUCT(INDIRECT(ADDRESS({8,9,13,17},14)),INDIRECT(ADDRESS({8,9,10,10},15,1,1,LOOKUP(L110,{0,10,20,30},{"1+","10+""20+","50+"}))))
But not sure if the functions and method are compatible, I don't have time to test it now, just thought I would throw it onto the suggestion pile before I leave.
I have almost achieved what I was looking for using the following:
=IF(L110<10,(('1+'!O8+'1+'!F8)*ORDER!N8)+(('1+'!O9+'1+'!F9)*ORDER!N9)+(('1+'!O10+'1+'!F10)*ORDER!N13)+(('1+'!O10+'1+'!M10)*N17)+(('1+'!O28+'1+'!F28)*ORDER!N51)+(('1+'!O28+'1+'!M28)*ORDER!N54)+(('1+'!O50+'1+'!F50)*ORDER!N101)+(('1+'!O51+'1+'!F51)*ORDER!N102),IF(L110>=100,(('100+'!O8+'100+'!F8)*ORDER!N8)+(('100+'!O9+'100+'!F9)*ORDER!N9)+(('100+'!O10+'100+'!F10)*ORDER!N13)+(('100+'!O10+'100+'!M10)*N17)+(('100+'!O28+'100+'!F28)*ORDER!N51)+(('100+'!O28+'100+'!M28)*ORDER!N54)+(('100+'!O50+'100+'!F50)*ORDER!N101)+(('100+'!O51+'100+'!F51)*ORDER!N102),IF(L110>=50,(('50+'!O8+'50+'!F8)*ORDER!N8)+(('50+'!O9+'50+'!F9)*ORDER!N9)+(('50+'!O10+'50+'!F10)*ORDER!N13)+(('50+'!O10+'50+'!M10)*N17)+(('50+'!O28+'50+'!F28)*ORDER!N51)+(('50+'!O28+'50+'!M28)*ORDER!N54)+(('50+'!O50+'50+'!F50)*ORDER!N101)+(('50+'!O51+'50+'!F51)*ORDER!N102),IF(L110>=20,(('20+'!O8+'20+'!F8)*ORDER!N8)+(('20+'!O9+'20+'!F9)*ORDER!N9)+(('20+'!O10+'20+'!F10)*ORDER!N13)+(('20+'!O10+'20+'!M10)*N17)+(('20+'!O28+'20+'!F28)*ORDER!N51)+(('20+'!O28+'20+'!M28)*ORDER!N54)+(('20+'!O50+'20+'!F50)*ORDER!N101)+(('20+'!O51+'20+'!F51)*ORDER!N102),(('10+'!O8+'10+'!F8)*ORDER!N8)+(('10+'!O9+'10+'!F9)*ORDER!N9)+(('10+'!O10+'10+'!F10)*ORDER!N13)+(('10+'!O10+'10+'!M10)*N17)+(('10+'!O28+'10+'!F28)*ORDER!N51)+(('10+'!O28+'10+'!M28)*ORDER!N54)+(('10+'!O50+'10+'!F50)*ORDER!N101)+(('10+'!O51+'10+'!F51)*ORDER!N102)))))
I now have an issue with rounding ...
In sheet 100+ .... cell F8 = £16.06
In sheet 100+ .... cell O8 = £32.80
these combined equal £48.86 .... multiple by 100 is £4886
Why is it that when I order 100 of cell F8+O8 in my sheet called ORDER, does this IF statement produce £4885.90
are those cells directly imputed like that? F7=16.06, O8=32.80? or are those cell calculated off different cells that are divided but other cells? ie.. N13=13.25, M28=21.71 (just an example) where those are then rounded through cell formatting to the nearest 2 decimal places
Hi
They are calculations :/
F8 = ='160101'!R8
O8 = =('160101'!U$11+'160101'!U$5+'160101'!U$8)*(1+'160101'!Y$1)
Change the cell formatting of O8 to increase decimal accuracy and you will see why this is happening.
The result in O8 is not actually 32.80, but 32.799, naturally you would round that to 32.80, which excel does visually, but it retains the original accuracy in the background, which passes to subsequent formulas.
There are several ways to 'fix' this, it would be down to you to choose the one best for your needs.
See here, https://support.office.com/en-gb/art...ad=GB&fromAR=1
Please be aware that any constants in your file (cells that contain actual values, not formulas) which have a decimal accuracy higher than shown by the current formatting will also be affected by the use of precision as displayed. Any such values affected by the use of precision as displayed cannot be returned to their original accuracy by unticking the box, or changing the format afterwards. Once done, it is irreversible!
Saving an extra backup copy is highly recommended.
Thanks! This seems to have done the trick..
Not quite sure I understand your second to last sentence? (highlighted bold)
All of my constants seem to be untouched so I think I'm all clear .....
If you type 1.9999 into a cell, then format that cell to 2 decimal places it will show as 2.00
If you apply precision as displayed, then it will be stuck at 2.00, there is no way to get the original value of 1.9999 back.
Does that make more sense?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks