enter a maximum in a formula?

John Caines · Feb 9, 2011

Hello All,
Houston, we have a problem!

I've tried to create a spreadsheet that shows some price earnings projections
over the year.

I've managed to get it working ok (My excel formula knowledge is very limited),
but I think all is ok.

Thing is;
I want to add a few advanced features to it, and have the results reflected
into another table alongside the original.
easier if I upload my spreadsheet really,,, here it is;
It's excel 2010 version;

http://tinyurl.com/69j6shj

There are 4 cells I'm trying to make work, and to be honest, I don't know
how and could really need some help.

1. Cell S6

2. Cell S15

3. Cell S18

4. Cell S21

Hopefully if you open the spreadsheet it should make sense what I'm looking to do.

For now, I'll just mention Cell S6, ie,, 1 step at a time

In cell S6 someone would enter a £ amount as a kind of cap (It's actually a daily
target figure).
This relates to formulas from cell's AA38:AA288

At the moment Cell AA38 says;

Code:

=IF(COUNTBLANK($AA$6:$AA$7),"",Z38*$AA$37)

As a note Z38 is a starting account equity amount, ie £10,000
Cell AA37 is a Daily profit goal %

So, what I need the formula to say for example in cell AA38 is;
If the amount returned is equal to or more than cell S6, then only display
the S6 amount.
If it's less than the data in S6,, its ok.

(At least I think that's what I need

I did try a couple of guesses, but nothing worked,ie;

Code:

=IF(COUNTBLANK($AA$6:$AA$7),"", IF(Z38*$AA$37>=S6,S6))

just shows the word FALSE.

Can anyone help me with this please.
I'd really like to get this to work, but I just don't know how.
Again, here's the link to my spreadsheet

http://tinyurl.com/69j6shj

Many thanks for all your time.

Best regards
john
PS, I know I didn't mention cells S15,S18,S21,, but I thought it was
best just to get over this hurdle 1st

Again many thanks to you all

John Caines · Feb 9, 2011

Would it need an array Sum IF formula???

I'm just trying to wing it in a text file,, but nothing is working,, and
I'm not sure if this is whats needed anyway,, but just thought I'd mention it

John Caines · Feb 9, 2011

This is doing my head in

I'm trying now all as guesses;

Code:

=IF(COUNTBLANK($AA$6:$AA$7),"",(Z38*$AA$37),IF(Z38*$AA$37,">=",S6),S6)

Hopefully meaning;
multiply z38 by aa37,,, but if z38 x aa37 is greater than or equal to s6,, just show the s6 £ amount...
Is it working???????
NO

VoG · Feb 9, 2011

Hi John. It would be something like

=MIN(S6,Z38*$AA$37)

John Caines · Feb 9, 2011

Hi VOG,,,Brilliant!!!!
Seems to work,, trying it all out now,,

Just need to do the other 3 cells now,,
S15,S18 & S21,,,

It really was doing me in,, I can't believe it is something totally different
than I thought formula wise,,,
I must watch the Lynda excel training essentials dvd I think

Hope you are well.

Again, thanks for coming to the rescue VOG,,
All the best
john

Again
Many thanks indeed

John Caines · Feb 9, 2011

As a note;
I did also post here;
http://www.excelforum.com/excel-general/763786-enter-a-maximum-in-a-formula.html

Forgot to mention it,,, I mentioned it on excel forum, but forgot to mention it here,,

usafmeinweg,,
on excelforum replied also with a formula that works VOG,,,
so for cell AA38 would read;

Code:

=IF(COUNTBLANK($AA$6:$AA$7),"",IF(Z38*$AA$37>$S$6,$S$6,Z38*$AA$37))

Thing is now VOG,, I really would like to get the other 3 cells working.... any ideas?

Cell S15 allows you to insert another £ amount,, which would be a increase from week 13
Cell S18 allows you to insert another £ amount,, which would be a increase from week 25
Cell S21 allows you to insert another £ amount,, which would be a increase from week 37

So,, basically Quarterly month increase that override the formulas that are now in the range; AA38:AA288

So now,, going with your formula VOG,,,from week 13,, the formula in this cell (CellAA101)
reads;

Code:

=IF(COUNTBLANK($AA$6:$AA$7),"",MIN(S6,Z101*$AA$37))

& lets say it shows an amount of £181.67,,

If cell S15 (The 2nd Quarter Yearly Increase If Entered)
has an amount of say £200 in it,,,
What would the formula now be for Cell AA101?

I'm trying it now VOG,, my brain is rattling

Logically it must say,,
Cell AA101 = Z101 x AA37,, but if it's greater than cell S6,, then it must equal S6,,,
BUT NOW,, if an amount is entered into cell S15,, it must equal cell S15..

ummmmmmmmmmm

Can't get it,,
I'm trying though,, well,,, people do say I'm very trying

VoG · Feb 9, 2011

John. I haven't downloaded your file but the general rule that you need to apply is

=MIN(my formula, cell containing the limit)

John Caines · Feb 9, 2011

Thanks VOG ,,
you originally posed;

Code:

MIN(S6,Z101*$AA$37))

But you say,
=MIN(my formula, cell containing the limit)

So shouldn't it be;

Code:

MIN(Z101*$AA$37,S6))

???
They both work VOG,, but for clarity,, which 1 is correct?

As to my Cell AA101 problem.
The formula reads;

Code:

=IF(COUNTBLANK($AA$6:$AA$7),"",MIN(S6,Z101*$AA$37))

But now I'm saying VOG,, if an amount is now ALSO entered into cell S15, (A Quarterly Increase Cell) (Say for example £200),, I need Cell AA101 to now display £200, that's if it reads less than £200.
That's the problem I now face VOG,,,

I've just tried;

Code:

=IF(COUNTBLANK($AA$6:$AA$7),"",MIN(Z101*$AA$37,S6),MAX(S15))

Pure guess,, not even sure what it means,, just trying different guesses

So it is saying ,,, carry on displaying whatever is in cell AA101,,, but,,,
If an amount of whatever is entered into cell S15 (Example £200) ,, display £200 now in cell AA101,,, but only if the figure that is in cell AA101 is less than £200.

I hope this makes sense VOG,,,
It's a pain,,,
but the sheet is looking good.
Many thanks again for your help VOG,,
very much appreciated.
All the best,
John

enter a maximum in a formula?

John Caines

Well-known Member

John Caines

Well-known Member

John Caines

Well-known Member

VoG

Legend

John Caines

Well-known Member

John Caines

Well-known Member

VoG

Legend

John Caines

Well-known Member

Similar threads

Share this page

enter a maximum in a formula?

Well-known Member

Well-known Member

Well-known Member

Legend

Well-known Member

Well-known Member

Legend

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock