enter a maximum in a formula?

[theghost]

Hello All,
For cross posting reference I have also posted here,
http://www.mrexcel.com/forum/showthread.php?t=527802

I'm really struggling with a formula to basically say;
"Multiple amount a and b, but only if its less than the amount in cell xyz.
if its the same or more than the amount in cell xyz, please just show the amount xyz.

Simple eh? :-)
can I figure it out?
No :-(
The formula I have in the cell I need altered (Which I can copy down the sheet) is cell
AA38 which reads

Please Login or Register  to view this content.

As a note , cell Z38 is a rolling account equity amount that increases down the sheet.
Cell AA37 is a Daily % increase.

Cell s6 is the amount that the formula needs to be capped by.
So for example; if the above formula generated an amount of say £600,,,, but now in cell
S6 I enter a figure of say £500,,,, Cell AA38 can now only show the amount of £500.

I hope this makes sense.

I did try as a guess;

Please Login or Register  to view this content.

I know nothing about arrays,, but maybe something like this is needed?

Please Login or Register  to view this content.

meaning, I thought, if z38 *AA37 is greater than cell S6, then display s6, if not,,, leave it as is.

I didn't quite get it :-)

Can anyone help me with this please.
Here's a link to my spreadsheet also,, so it should make sense.
http://tinyurl.com/69j6shj

Many thanks to you all,
I hope someone can shed some light on this for me
Again, many thanks,
TheGhost

[theghost]

This is doing my head in :-)

I'm trying now all as guesses;

Please Login or Register  to view this content.

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 :-(

[usafmeinweg]

can you attach a sample spreadsheet with some data and I can give it a try for ya. I can't access the tinyurl link from my server

[theghost]

Many many thanks for taking a look usafmeinweg,,,
I've attached the file../
Hope it all makes sense

Great stuff,,,,
Its really really got me this one.
Again ,,,
many Thanks

TheGhost

[usafmeinweg]

I think that I got what you needed. I pasted the values in AA38:AA288

[theghost]

Many thanks for your help with this usafmeinweg
I see the code,,, you have for cell AA38

Please Login or Register  to view this content.

That's great,,,
I've just got another reply from VOG on Mr.Excel
his is;

Please Login or Register  to view this content.

Seems to work also.....

Thing is,, I need to try now and see how these all work with S15,S18 & S21 working..
Again, thanks for this usafmeinweg,,,,,
maybe your formula can be adjusted for these other 3 cells?

[usafmeinweg]

If I understand your workbook....Qtr 2 has to be less than the daily cap of 500. So if I input 200 into Qtr2 then weeks 13-24 can't exceed that 200. Am I getting the idea of what you are looking for?

[theghost]

yes usafmeinweg,,
that's right,,,, kind of,,,,,,,,,

If nothing is entered into quarter 3 or 4,,, though,,, quarter 2,, say for example an amount of £200 from week 13 would just run down the sheet being multiplied by the daily profit %.
AND,,,, (If The MAXIMUM CAP was in place),, would be capped by that amount when hit,,,,

So,, in answer to your question,,,,,,,
Quarter 2 STARTS from week 13,,, but doesn't finish at week 24,, I think that is better. No ending time for any quarter,,,,

This way it gives you the option to have 1-2 or 3 increases that just continue,,,
I hope this makes sense?
But if quarter 3 was entered also,, quarter 2 would stop at week 24,, because quarter 3 would then start at week 25 and continue down the sheet

Again many thanks for trying to help me with this usafmeinweg,,,
It is getting there.
I hope it all makes sense
The Ghost

[usafmeinweg]

See if the attached does what you want it to do

[theghost]

Many thanks for your reply usafmeinweg,,

Its great,, it actually is perfect for a fixed fractional model,,
I have on another sheet,, so these formulas work 100% for that,, perfect.

But for this % based model,,,It's almost,,, but not quite....
As you have it now,, week 13 Cell AA101 displays £200,,
this is correct.
But Cell AA102 needs to display not £200,, but £200 (MULTIPLIED) by The Profit goal Figure in Cell AA37.
This would spiral, continue down the sheet, each new amount being multiplied by the % figure in cell AA37

I think I did mention it,,,, let me see,,, yep, I mentioned it here usafmeinweg,

If nothing is entered into quarter 3 or 4,,, though,,, quarter 2,, say for example an amount of £200 from week 13 would just run down the sheet being multiplied by the daily profit %.

Again, many thanks for this,, I will use what you have written anyway for the fixed fractional sheet I have as this is perfect,,,

But this just needs tweaking,, it's 95% correct,, just needs to multiply by the Daily profit goal % Cell AA37

I hope all the above makes sense usafmeinweg,,
many thanks again.

All the best
A very grateful
John C :-)