[Help] MS Excel

[vortex]

Is it possible to set a maximum value that will be displayed in a cell, even if the forumla within the cell surpasses this maximum value?

E.g. I have a formula that gives me numbers above 100 sometimes and I can't help the fact that it does, but is there a way to 'cap' the cell so that any number over 100 will be displayed as 100?

If it is possible (which im thinking it's not), when I use that cell in other formulas, will it pull the real value, or the 100? Thanks.

 

[DMAUL]

if(answer > 100, 100, answer)

 

[vortex]

now when I use that cell in other formulas, will it calculate with 100, or with the 'real' number?

Nevermind, I just tested some numbers and it uses 100, I love you D <3

 

[DMAUL]

properly calculated

 

[vortex]

Sorry for the bump, but is there a way I can make undefined values (i.e. values divided by zero) show up as zero in the cell rather than #DIV/0!? It's screwing up some of my stat averaging...

 

[Cuda]

if(iserror(cell), 0)

 

[cyclozine]

uhh
min(x,100)

 

[vortex]

Thanks Cuda

Edit: This is what you guys helped me produce here...

=IF(ISERROR(IF((($H$4+$H$5-$H$6)/500)*B$6>100,100,(($H$4+$H$5-$H$6)/500)*B$6)), 0, IF((($H$4+$H$5-$H$6)/500)*B$6>100,100,(($H$4+$H$5-$H$6)/500)*B$6))

<3 to you both

 

[Snake~eyes]

These guys excel at Excel! LOLOLOLOLOLOLOL

 

[Wish Bear]

excel is somewhat powerful if you know how to use it right...cudos to those excel mofos

 

[Cuda]

excel is somewhat powerful if you know how to use it right...cudos to those excel mofos

Excel is insanely powerful if you know how to use it well. I'm going to make a workbook that will import all the ingredients for recipes and spit out how much the recipes cost for each dish. Once done, it will bring a process that took probably close to ten hours down to about 30 seconds.

What the hell are you doing anyway?

 

[tdshiv]

i love excel. it's my friend.

 

[vortex]

What the hell are you doing anyway?

If you were asking me what I'm doing...

I'm creating a statisics spreadsheet for 34 different maps in America's Army. On each map's sheet, it says how many kills you need to increase your fragrate on the tracker (http://aaotracker.4players.de). I've also duplicated the Activity/Popularity/Experience bars. As I said though, the basic function is to tell me how many kills I will need to increase my fragrate on each map. (if you die X amount of times in the session, it will tell you the number of kills you still need to get your desired fragrate).

In all, it's pretty basic. Just duplicating those Activity/Popularity/Experience bars required the stuff you guys helped me with

Edit: Sorry for bumping again, but if you care, it's here.